import ctypes
ctypes.windll.user32.ShowWindow(ctypes.windll.kernel32.GetConsoleWindow(), 0)
#上面两行确保批处理运行时不显示CMD窗口
import tkinter as tk
from tkinter import Label
from tkinter import ttk
import tkinter.messagebox
import datetime
from turtle import color
import pymysql
import time
import getpass
import openpyxl as vb
from openpyxl.worksheet.datavalidation import DataValidation  #单元格设置固定选择项
import sys
import numpy as np
import os
import re
import math
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage
import matplotlib.pyplot as plt

from zeep import Client




plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']  #支持图中的中文显示




def main():
    
    #窗体
    window = tkinter.Tk()
    window.title('TCC Reliability Operation Award/Audit Record Tool              '+'V'+str(version)+' '+release_date)
    window.configure(bg='whitesmoke')   


    global user_name
    global rownumber
    rownumber = 1  #为了new中区分是显示的submitted 还是首次就使用new
  
    #获取用户名
    user_name = getpass.getuser()  

    #写标题行(8列)
    #sheet是Text文本框
    title = ['No','Category','Technician', 'Engineer', 'Product', 'Test_no', 'Test_name', 'Description','Score(+-1)']   
    for c in range(9): 
        sheet = tkinter.Text(window,width=10,height=2)#创建一个text窗口
        sheet.configure(font=("Courier", 10, "italic"),backgroun="LemonChiffon", foreground="blue")
        if c==0:
            sheet = tkinter.Text(window,width=5,height=2)#创建一个text窗口
            sheet.configure(font=("Courier", 10, "italic"),backgroun="LemonChiffon", foreground="blue")
        if c==7:
            sheet = tkinter.Text(window,width=35,height=2)#创建一个text窗口
            sheet.configure(font=("Courier", 10, "italic"),backgroun="LemonChiffon", foreground="blue")
        sheet.insert('end',title[c])#在刚刚创建的窗口里写入默认的文本
        sheet.grid(row=0,column=c)#将该text窗口放在第r行，第c列
        sheet.configure(state='disabled')

    


    def display(): 
        display_days=30  #显示一个月内的输入记录，但是要限制显示15条
        pre_date=(datetime.datetime.now()+datetime.timedelta(days=-display_days)).strftime('%Y-%m-%d') 
        today=datetime.datetime.now().strftime('%m/%d')
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')     
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor() 
        #如果rownumber>1,说明曾经显示过数据，则先清空整个表格，仅保留第0行的标题 ,用于更新数据后重新点display的情况
        global rownumber       
        if rownumber>1:       
            for r in range(1,rownumber+1):
                for c in range(9):
                    index=str(r)+str(c)
                    globals()['sheet'+index].grid_forget()      

        if user_name=='lijun24' or user_name=='wangjin24' or user_name=='nizhao2' or user_name=='lizh48':           


            cursor.execute('''select No, Category, Technician, Engineer, Product, Test_no, Test_name, Description,score from auditrecord 
            ORDER BY NO DESC LIMIT 15''')                
            rows=cursor.fetchall()     

        else:        
   
            #再获取所有数据:  按日期顺序，最多获取本人在30天内的最后15条输入记录
            # cursor.execute('''select No, Category, Technician, Engineer, Product, Test_no, Test_name, Description,score from auditrecord 
            # where auditor = %s and date >= %s order by date desc limit %s''', (user_name, pre_date,rownumber)) 
            cursor.execute('''select No, Category, Technician, Engineer, Product, Test_no, Test_name, Description,score from auditrecord 
            where auditor=%s ORDER BY NO DESC LIMIT 15''', user_name)      

            rows=cursor.fetchall()    



        rownumber=15
        #逐一写入文本框
        
        for r in range(1,rownumber+1):
            for c in range(9):
                index=str(r)+str(c)
                globals()['sheet'+index] = tkinter.Text(window,width=10,height=2)#创建一个text窗口  globals()[]实现动态变化的变量名
                globals()['sheet'+index].configure(font=("Courier", 10, "roman"),backgroun="White", foreground="Black")
                if c==0:
                    globals()['sheet'+index] = tkinter.Text(window,width=5,height=2)#创建一个text窗口
                    globals()['sheet'+index].configure(font=("Courier", 10, "roman"),backgroun="White", foreground="Black")
                if c==7:
                    globals()['sheet'+index] = tkinter.Text(window,width=35,height=2)#创建一个text窗口
                    globals()['sheet'+index].configure(font=("Courier", 10, "roman"),backgroun="White", foreground="Black")
                globals()['sheet'+index].insert('end',rows[r-1][c])#在刚刚创建的窗口里写入默认的文本, 这里的-1是因为fetchall取得的数据是从0开始的
                globals()['sheet'+index].grid(row=r,column=c)#将该text窗口放在第r行，第c列
                if c==0:
                    globals()['sheet'+index].configure(state='disabled')
        
        cursor.close()
        conn.close()    

            
    def auto_email(category,technician,description,user_name):
        cursor.execute('''select mailbox from collection where name=%s''',technician)
        mail_id=cursor.fetchone()
        mailaddress=mail_id[0]+'@lenovo.com'
        usermail=user_name+'@lenovo.com'
    

        from_addr='tcc_reliability@motorola.com'
        to_addr=['lijun24@lenovo.com','nizhao2@lenovo.com','wangjin24@lenovo.com','lizh48@lenovo.com','zhangyu90@lenovo.com']                       
 
        to_addr.append(mailaddress) 
        to_addr.append(usermail) 


        # 构建message
        msg = MIMEMultipart()              

        msg['From'] = Header(from_addr)
        msg['To'] = Header(",".join(to_addr))  
        header_name ='Award加分通知： '+ technician
        msg['Subject'] = Header(header_name,'utf-8') #utf-8可以不加


        # 以html格式构建邮件内容
        send_str = '<html><body>'
        send_str += '<left>' +'<font size="4" color="red">'+'  Award 加分已被记录！'+'</font>' +'</left>'      
        send_str += '<center> </center>'  
        send_str += '<left>'+'*姓名：'+technician+'</left>'          
        send_str += '<center> </center>'
        send_str += '<left>'+'*类别：'+category+'</left>'          
        send_str += '<center> </center>'
        send_str += '<left>'+ '*加分: '+'<font size="4" color="red">'+ '+1' +'</font>'+'</left>'
        send_str += '<center> </center>'   
        send_str += '<left>'+ '*加分描述: '+ description +'</left>'
        send_str += '<center> </center>'   
        send_str += '<left>'+ '*加分人: '+ user_name +'</left>'
        send_str += '<center> </center>'   

        send_str += '<left>' +'<font size="3" color="blue">'+'  如对加分有疑问请直接联系加分人！'+'</font>' +'</left>'       

        send_str += '</body></html>'

        # 添加邮件内容
        content = MIMEText(send_str, _subtype='html', _charset='utf8')
        msg.attach(content)

        # API 地址
        api_url = 'http://100.64.5.171/WebServices/WS_EMAIL/WS_EMAIL.asmx?wsdl'
        email_params = {
            'message':  msg.as_string()  # 将邮件内容转换为字符串
        }

        # 发送邮件
        try:
            client = Client(api_url)
            response = client.service.sendEmail(**email_params)
            msg=("Email sent successfully:", response)
        except Exception as e:
            msg=("Failed to send email:", e)
        print(msg)  
  



    def update():
        today=datetime.datetime.now().strftime('%m/%d')
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')     
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor()
        # category_list=['报告质量','实验操作','样机安全','运营支持','测试支持']
        mark=0
        #分几种情况：        
        # 1，Display之后的修改（包括修改和删除行）：此时rownumber>=1，No是数字，根据No对应更新数据库，如果technician列没人，则删除数据库中该行
        # 2. New输入之后的提交：此时rownumber不一定是1，如果之前display过就不是1，因此提交后要break ，No为空，非数字。此时Category, technician, description非空，才会插入到数据库
        for r in range(1,rownumber+1):
            templist=[]
            for c in range(9):
                index=str(r)+str(c)
                result=globals()['sheet'+index].get(1.0,'end')    #获取文本框输入的内容                
                templist.append(result.strip())   #从文本框读到的数据后面会有一些换行符，.strip()去掉字符串结尾的换行符和空格
            # print(templist) 

            #属于情况1：针对今日已经提交的submitteed的修改，这时No是数字。针对对应的DB 中的NO行进行更新
            if templist[0].isdigit():      
                cursor.execute('update auditrecord set Category=%s, Technician=%s,Engineer=%s,Product=%s,Test_no=%s,Test_name=%s,Description=%s,Score=%s where No=%s',
                (templist[1],templist[2],templist[3],templist[4],templist[5],templist[6],templist[7],templist[8],templist[0]))
                conn.commit()
                #如果Technician列没有人，则删除这一行
                if templist[2]=='':
                    cursor.execute('delete from auditrecord where No=%s',templist[0])
                    conn.commit() 

               
                

            #情况2：针对News输入的提交，这时No无法输入，由此来确定它是新的输入
            else:
                query = '''insert into auditrecord (Category, Date, Technician,Engineer,Product,Test_no,Test_name,Description,Auditor,Score)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''' 
    
                values = (templist[1],todaycolumn,templist[2],templist[3],templist[4],templist[5],templist[6],templist[7],user_name,templist[8])
                
                
                #确保至少category,technician,description三个格子里都有内容才可以提交
                if templist[1]!='' and templist[2]!='' and templist[3]!='' and templist[7]!='' and templist[8]!='':
                    cursor.execute(query,values) 
                    conn.commit() 

                    category=templist[1]
                    technician=templist[2]
                    description=templist[7]
                    score=templist[8]
                    if score=='1':
                        auto_email(category,technician,description,user_name)
                    
                else:
                    tkinter.messagebox.showwarning(title='Hi', message='填写信息不全或格式有误！')
                    mark=1
                    break
            
        if mark==0:
            tkinter.messagebox.showinfo(title='Hi', message='更新数据成功！')        
                    
        cursor.close()
        conn.close() 

    def new():
        
        #如果rownumber>1,说明有2行以上数据，则先清空整个表格，仅保留第0行的标题         
        if rownumber>1:       
            for r in range(1,rownumber+1):
                for c in range(9):
                    index=str(r)+str(c)
                    globals()['sheet'+index].grid_forget()   

        
        #在第一行写一个空格行
        for r in range(1,2): #只重复1次，r只等于1
            for c in range(9):
                index=str(r)+str(c)
                globals()['sheet'+index] = tkinter.Text(window,width=10,height=2)#创建一个text窗口
                globals()['sheet'+index].configure(font=("Courier", 10, "roman"), backgroun="White", foreground="Black")
                
                if c==0:
                    globals()['sheet'+index] = tkinter.Text(window,width=5,height=2) 
                    globals()['sheet'+index].configure(font=("Courier", 10, "roman"),backgroun="White", foreground="Black")
               
                if c==7:
                    globals()['sheet'+index] = tkinter.Text(window,width=35,height=2) 
                    globals()['sheet'+index].configure(font=("Courier", 10, "roman"),backgroun="White", foreground="Black")
                globals()['sheet'+index].insert('end','')  
                globals()['sheet'+index].grid(row=r,column=c) #将该text窗口放在第r行，第c列
                if c==0:
                    globals()['sheet'+index].configure(state='disabled')
        
        #执行New添加一个空行之后在上面的一些格子里放置comboxlist

        def go(*args):  #处理事件，*args表示可变参数  #可以放在Win=thinter.TK语句之后
            print(comboxlist.get()) #打印选中的值
            input=comboxlist.get()
            r=1
            c=1
            index=str(r)+str(c)
            globals()['sheet'+index].delete('1.0','end') 
            globals()['sheet'+index].insert('end',input) 

        def go1(*args):  #处理事件，*args表示可变参数  #可以放在Win=thinter.TK语句之后
            print(comboxlist1.get()) #打印选中的值
            input1=comboxlist1.get()
            r=1
            c1=3
            index1=str(r)+str(c1)
            globals()['sheet'+index1].delete('1.0','end') 
            globals()['sheet'+index1].insert('end',input1)     
        def go2(*args):  #处理事件，*args表示可变参数  #可以放在Win=thinter.TK语句之后
            print(comboxlist2.get()) #打印选中的值
            input2=comboxlist2.get()
            r=1
            c2=8
            index1=str(r)+str(c2)
            globals()['sheet'+index1].delete('1.0','end') 
            globals()['sheet'+index1].insert('end',input2)  

        def go3(*args):  #处理事件，*args表示可变参数  #可以放在Win=thinter.TK语句之后
            print(comboxlist3.get()) #打印选中的值
            input3=comboxlist3.get()
            r=1
            c3=2
            index1=str(r)+str(c3)
            globals()['sheet'+index1].delete('1.0','end') 
            globals()['sheet'+index1].insert('end',input3)  

        comvalue=tkinter.StringVar()#窗体自带的文本，新建一个值
        comboxlist=ttk.Combobox(window,textvariable=comvalue,width = 8, height = 18 ) #初始化
        comboxlist["values"]=("实验质量","5S&低耗","设备管理","纪律管理","样机管理","测试支持","课题研究","运营支持","反馈建议")
        comboxlist["state"]='readonly' #不可输入其它内容
        comboxlist.current(0) #选择第一个
        comboxlist.bind("<<ComboboxSelected>>",go) #绑定事件,(下拉列表框被选中时，绑定go()函数)
        comboxlist.grid(row=1,column=1,ipady=3)
    
        comvalue1=tkinter.StringVar()#窗体自带的文本，新建一个值
        comboxlist1=ttk.Combobox(window,textvariable=comvalue1,width = 8, height = 18) #初始化
        comboxlist1["values"]=('赵俊凯','汪焕','陈凯','张永庆','熊春强','彭彩虹','董胜','倪昭','王锦','李紫华','徐航','翟伟峰','张长兴','刘进','廖秋华','N/A')
        comboxlist1["state"]='readonly' #不可输入其它内容
        comboxlist1.current(0) #选择第一个
        comboxlist1.bind("<<ComboboxSelected>>",go1) #绑定事件,(下拉列表框被选中时，绑定go()函数)
        comboxlist1.grid(row=1,column=3,ipady=3)
    
        comvalue2=tkinter.StringVar()#窗体自带的文本，新建一个值
        comboxlist2=ttk.Combobox(window,textvariable=comvalue2,width = 8, height = 18) #初始化
        comboxlist2["values"]=("1","-1")
        comboxlist2["state"]='readonly' #不可输入其它内容
        comboxlist2.current(0) #选择第一个
        comboxlist2.bind("<<ComboboxSelected>>",go2) #绑定事件,(下拉列表框被选中时，绑定go()函数)
        comboxlist2.grid(row=1,column=8,ipady=3)



        #获取technician列表
        tech_startdate='2023-01-01'
        
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor()
        technician_list =[]                 

        cursor.execute('''SELECT technician FROM auditrecord where date>=%s group by technician ORDER BY convert(technician using gbk) asc''',tech_startdate)
        tech_names=cursor.fetchall()  
        for i in tech_names:
            technician_list.append(i[0])


        comvalue3=tkinter.StringVar()#窗体自带的文本，新建一个值
        comboxlist3=ttk.Combobox(window,textvariable=comvalue3,width = 8, height = 18) #初始化
        comboxlist3["values"]=technician_list
        comboxlist3["state"]='readonly' #不可输入其它内容
        comboxlist3.current(0) #选择第一个
        comboxlist3.bind("<<ComboboxSelected>>",go3) #绑定事件,(下拉列表框被选中时，绑定go()函数)
        comboxlist3.grid(row=1,column=2,ipady=3)

    def email_summary():
        #---生成图形-------------------------------------
        today=datetime.datetime.now().strftime('%m/%d')
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')  
        startdate='2024-04-01'

        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor()

        countnumber=[]
        # engineer_name_list = ['王锦','永庆','陈凯','汪焕','俊凯','倪昭']  
        engineer_name_list =[]                 

        cursor.execute('''SELECT COUNT(*), auditor FROM auditrecord where auditor!='g18978' and auditor!='wenzm2' and date>=%s GROUP BY auditor ORDER BY COUNT(*) desc ''',startdate)
        count_names=cursor.fetchall()  
        for i in count_names:
            countnumber.append(i[0])
            engineer_name_list.append(i[1])

        #创建一个figure（一个窗口）来显示条形图
        plt.figure(figsize=(10,18))        
        
        #---图1
        plt.subplot(311)
        #设置坐标刻度字体大小
        plt.xticks(fontsize=8,rotation=45)
        plt.yticks(fontsize=9)
        # plt.yticks(np.arange(0, 15, 1))
        #plt.ylim(0,12)  #y轴刻度最小到最大
        #plt.yticks([0,5,8,10,12]) #y轴刻度，不加按默认

        plt.bar(engineer_name_list,countnumber, label='记录数量',color='Green')
            
        plt.legend(fontsize=8) #里面设置字体大小没管用，无论是数值还是fontsize='small'都不行
        # plt.legend(loc="upper right",fontsize='x-small')  #本句是否可以替代上句，不确定！
        #plt.xlabel('name',fontsize = 6)
        plt.ylabel('Record number',fontsize = 9)
        
        plt.title('Award/Audit Record Summary (Since from 20240401)',fontsize = 12)

            # #   #柱子上面显示数值(zip里面放3个参数我独创！！)
        for x,y in zip(engineer_name_list,countnumber):        
            plt.text(x,y,'%.0f' %y, ha='center',va='bottom',fontsize=8) 

        #---图2
        # startdate='2021-04-01'
        Auditor_name_list = []
        total_negative_score = []
        total_positive_score = []
        #获取打分人名单，只有打过负分的人才会被选中
        cursor.execute('''SELECT Auditor, SUM(Score) from auditrecord where score=-1 and DATE>=%s group by Auditor ORDER BY SUM(Score)''',startdate)
        negative_info=cursor.fetchall()
        #获取工程师名单，以及手头总工时
        for n in negative_info:      
            Auditor_name_list.append(n[0])
            total_negative_score.append(n[1])

        
        #根据获取的Auditor名单，获取    
        for name in Auditor_name_list:
            #取得每个人的总工时 
            cursor.execute('''SELECT COUNT(*) from auditrecord WHERE Score>=1 and DATE>=%s AND Auditor=%s ''',(startdate,name))
            positive_scores=cursor.fetchone()  
            if positive_scores[0] is not None:
                total_positive_score.append(positive_scores[0]) 
            else:
                total_positive_score.append(0)


        #创建一个figure（一个窗口）来显示条形图
        plt.subplot(312)  
        #设置坐标刻度字体大小
        plt.xticks(fontsize=8,rotation=45)
        plt.yticks(fontsize=9)
        #plt.ylim(0,12)  #y轴刻度最小到最大
        #plt.yticks([0,5,8,10,12]) #y轴刻度，不加按默认

        x =list(range(len(Auditor_name_list)))   #x是个列表，元素为0,1,2，。。。个数和工程师个数相同
        total_width, n = 0.6, 2
        width = total_width / n

        plt.bar(x, total_negative_score, width=width, label='减分',color='deepskyblue')
        
        for i in range(len(x)):   #第一个柱子画完后，为了使得第二个柱子的x轴位置偏移一些，因此对x这个列表中每个元素+width移位
            x[i] = x[i] + width
        plt.bar(x, total_positive_score, width=width, label='加分',tick_label = Auditor_name_list,color='tomato')
            
        plt.legend(fontsize=7) #里面设置字体大小没管用，无论是数值还是fontsize='small'都不行
        # plt.legend(loc="upper right",fontsize='x-small')  #本句是否可以替代上句，不确定！
        #plt.xlabel('name',fontsize = 6)
        plt.ylabel('Scores',fontsize = 8)
        plt.yticks(fontsize=8)
        # plt.title('加减分汇总',fontsize = 8)
        plt.grid(which='major', axis='x',color='gray',linewidth='0.1')

            # #   #柱子上面显示数值(zip里面放3个参数我独创！！)
        for x,y,z in zip(x,total_negative_score,total_positive_score):        
            plt.text(x-width,y,'%.0f' %y, ha='center',va='bottom',fontsize=5)        
            plt.text(x,z,'%.0f' %z, ha='center',va='bottom',fontsize=5)
        
        #---图3
        # startdate='2021-04-01'
        Technician_name_list = []
        total_negative_score = []
        total_positive_score = []

        #  获取技术员总名单，按加减分汇总和来排序，从负到正
        resigned=['董胜','冯志伟','马义波','王珍丽','李泽辉','谭露','陈书恒','李军','倪昭','陈义']

        cursor.execute('''SELECT Technician, SUM(Score) from auditrecord where Technician not in %s and DATE>=%s group by Technician ORDER BY SUM(Score)''',(resigned,startdate))
        negative_info=cursor.fetchall()
        
        for n in negative_info:      
            Technician_name_list.append(n[0])

        
        #根据获取的技术员名单，获取减分的次数   
        for name in Technician_name_list:
            #取得每个人的总工时 
            cursor.execute('''SELECT sum(Score) from auditrecord WHERE Score=-1 and DATE>=%s AND Technician=%s ''',(startdate,name))
            negative_scores=cursor.fetchone()  
            if negative_scores[0] is not None:
                total_negative_score.append(negative_scores[0]) 
            else:
                total_negative_score.append(0)

        #根据获取的技术员名单，获取加分的次数   
        for name in Technician_name_list:
            #取得每个人的总工时 
            cursor.execute('''SELECT COUNT(*) from auditrecord WHERE Score>=1 and DATE>=%s AND Technician=%s ''',(startdate,name))
            positive_scores=cursor.fetchone()  
            if positive_scores[0] is not None:
                total_positive_score.append(positive_scores[0]) 
            else:
                total_positive_score.append(0)


        #创建一个figure（一个窗口）来显示条形图
        plt.subplot(313)  
        #设置坐标刻度字体大小
        plt.xticks(fontsize=7,rotation=45)
        plt.yticks(fontsize=9)
        #plt.ylim(0,12)  #y轴刻度最小到最大
        #plt.yticks([0,5,8,10,12]) #y轴刻度，不加按默认

        x =list(range(len(Technician_name_list)))   #x是个列表，元素为0,1,2，。。。个数和工程师个数相同
        total_width, n = 0.6, 2
        width = total_width / n

        plt.bar(x, total_negative_score, width=width, label='减分',color='deepskyblue')
        
        for i in range(len(x)):   #第一个柱子画完后，为了使得第二个柱子的x轴位置偏移一些，因此对x这个列表中每个元素+width移位
            x[i] = x[i] + width
        plt.bar(x, total_positive_score, width=width, label='加分',tick_label = Technician_name_list,color='tomato')
            
        plt.legend(fontsize=7) #里面设置字体大小没管用，无论是数值还是fontsize='small'都不行
        # plt.legend(loc="upper right",fontsize='x-small')  #本句是否可以替代上句，不确定！
        #plt.xlabel('name',fontsize = 6)
        plt.ylabel('Scores',fontsize = 8)
        plt.yticks(fontsize=8)
        # plt.title('加减分汇总',fontsize = 8)
        plt.grid(which='major', axis='x',color='gray',linewidth='0.1')

            # #   #柱子上面显示数值(zip里面放3个参数我独创！！)
        for x,y,z in zip(x,total_negative_score,total_positive_score):        
            plt.text(x-width,y,'%.0f' %y, ha='center',va='bottom',fontsize=5)        
            plt.text(x,z,'%.0f' %z, ha='center',va='bottom',fontsize=5)

 





        plt.savefig('image_1.png',dpi = 300)
           #释放游标，断开数据库，释放资源
        cursor.close()
        conn.close()
        
          
     
        #----------------邮件发送--------------------------------------
        sender = 'TCC_notification@motorola.com'
        receivers = ['lijun24@lenovo.com','nizhao2@lenovo.com','wangjin24@lenovo.com','lizh48@lenovo.com','zhangyu90@lenovo.com']  # 接收邮箱，如果多个邮箱，用逗号分隔


        # 构建message
        msg = MIMEMultipart()

        msg['From'] = Header(sender)
        msg['To'] = Header(",".join(receivers))  
        # msg['Subject'] = Header('Award_Audit Record ') #utf-8可以不加
        msg['Subject'] = Header('Award_Audit Record V' +str(version)+' '+release_date+''+'Run by:'+user_name,'utf-8') #utf-8可以不加

        # 以html格式构建邮件内容
        send_str = '<html><body>'

        # 下面这个方法可以变换字体及颜色
        # send_str += '<left>'+ '<font size="3" color="red">'+str_name_t+'</font>' +'</left>'  
        send_str += '<center> <br></center>'  #有这行导致两图之间间距过大

        send_str += '<img src="cid:image1" alt="image1" align="center" width=80% >'
        send_str += '<center> </center>'  #有这行导致两图之间间距过大

        send_str += '<center> 本邮件在数据导出时发出。版本: V'+str(version)+' '+release_date+'</center>'
        send_str += '</body></html>'

        # 添加邮件内容
        content = MIMEText(send_str, _subtype='html', _charset='utf8')
        msg.attach(content)

        img1 = MIMEImage(open('image_1.png', 'rb').read(), _subtype='octet-stream')
        img1.add_header('Content-ID', 'image1')
        msg.attach(img1)

        # API 地址
        api_url = 'http://100.64.5.171/WebServices/WS_EMAIL/WS_EMAIL.asmx?wsdl'
        email_params = {
            'message':  msg.as_string()  # 将邮件内容转换为字符串
        }

        # 发送邮件
        try:
            client = Client(api_url)
            response = client.service.sendEmail(**email_params)
            print("Email sent successfully:", response)
        except Exception as e:
            print("Failed to send email:", e)




 
        # msg['X-Priority'] = '1'

        # smtpObj = smtplib.SMTP('smtpinternal.lenovo.com')  # smtpinternal.lenovo.com 对应的IP地址

        # try:
        #     smtpObj.sendmail(sender, receivers, msg.as_string())
        # except smtplib.SMTPException as smtpException:
        #     print(str(smtpException))
        # finally:
        #     smtpObj.quit()

        os.remove('image_1.png')  #用完删除图片

        plt.close('all')  #清内存


    
    
    def export():
        today=datetime.datetime.now().strftime('%m%d')
        tomorrow=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%m%d')
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')   
        tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d') 
        #建立mysql的连接
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor()

        pathfile = r'C:\自动分活儿\Audit Data Summary'+today+'.xlsx'
        workbook = vb.Workbook(pathfile)
        workbook.save(pathfile)
        workbook=vb.load_workbook(pathfile)
        worksheet1 = workbook.active


        fontstyle = vb.styles.Font(name='Times New Roman',bold=True,size=10)  #标题
        fontstyle1 = vb.styles.Font(name='Arial',bold=True,size=10)    #标题
        fontstyle2 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文红色
        fontstyle3 = vb.styles.Font(name='Arial',size=10,color='DC143C')  #正文红色
        alignstyle = vb.styles.Alignment(horizontal='left',vertical='center')  #居中
        cellbackground= vb.styles.PatternFill(fill_type='lightGray') 

        worksheet1['A1'] = 'No'  
        worksheet1['B1'] = 'Category'  
        worksheet1['C1'] = 'Date'
        worksheet1['D1'] = 'Technician'
        worksheet1['E1'] = 'Engineer'
        worksheet1['F1'] = 'Product'
        worksheet1['G1'] = 'Test_no'
        worksheet1['H1'] = 'Test_name'
        worksheet1['I1'] = 'Description'
        worksheet1['J1'] = 'Auditor'
        worksheet1['K1'] = 'Score'   



        cursor.execute('''select count(*) from auditrecord where (score=1 or score=-1) and date>='2023-04-01' ''')
        b=cursor.fetchone()  
        cursor.execute('''select * from auditrecord where (score=1 or score=-1) and date>='2023-04-01' ''')
        allrows=cursor.fetchall()  
        for row in range(2,b[0]+2):      
            worksheet1.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
            worksheet1.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle 
            worksheet1.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle 
            worksheet1.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle 
            worksheet1.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle 
            worksheet1.cell(row,6,value = allrows[row-2][5]).alignment = alignstyle             
            worksheet1.cell(row,7,value = allrows[row-2][6]).alignment = alignstyle 
            worksheet1.cell(row,8,value = allrows[row-2][7]).alignment = alignstyle 
            worksheet1.cell(row,9,value = allrows[row-2][8]).alignment = alignstyle 
            worksheet1.cell(row,10,value = allrows[row-2][9]).alignment = alignstyle 
            worksheet1.cell(row,10).font = fontstyle2  
            worksheet1.cell(row,11,value = allrows[row-2][10]).alignment = alignstyle 
            if allrows[row-2][10]==-1:
                worksheet1.cell(row,4).font = fontstyle3 
                worksheet1.cell(row,11).font = fontstyle3 


        for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1']:    
            worksheet1[cell].fill = cellbackground
            worksheet1[cell].font = fontstyle1
            worksheet1[cell].alignment = alignstyle

        # worksheet1['F1'].alignment = alignstyle 

        worksheet1.column_dimensions['A'].width = 5
        worksheet1.column_dimensions['B'].width = 12
        worksheet1.column_dimensions['C'].width = 12
        worksheet1.column_dimensions['D'].width = 11
        worksheet1.column_dimensions['E'].width = 12
        worksheet1.column_dimensions['F'].width = 15
        worksheet1.column_dimensions['G'].width = 12
        worksheet1.column_dimensions['H'].width = 15
        worksheet1.column_dimensions['I'].width = 45
        worksheet1.column_dimensions['J'].width = 11
        worksheet1.column_dimensions['K'].width = 10
       

        worksheet1.row_dimensions[1].height =20
        worksheet1.auto_filter.ref = "A1:K1"
        
        workbook.save(pathfile)  

        print('输出表格数据成功！')
        tkinter.messagebox.showinfo(title='Hi', message='输出表格数据成功！')
        email_summary()
        tkinter.messagebox.showinfo(title='Hi', message='邮件已发送！')


#下面为20241221从单独文件移过来

    def lastweek_report_export(ip_address):
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
        four_weeks_ago=(datetime.datetime.now()+datetime.timedelta(days=-31)).strftime('%Y-%m-%d')
        last_monday=(datetime.datetime.now() - datetime.timedelta(days=datetime.datetime.now().weekday() + 7)).strftime('%Y-%m-%d') 
        last_sunday=(datetime.datetime.now() - datetime.timedelta(days=datetime.datetime.now().isoweekday())).strftime('%Y-%m-%d') 
        print(last_monday,last_sunday)
        # now1='2020-03-29'
        # date_format = '%Y%m%d'
        # four_weeks_ago=datetime.datetime.strptime(four_weeks_ago_str,date_format)
        # print(now2)
        # print(now2.month)
        # print(now2.year)
        # print(four_weeks_ago,type(four_weeks_ago))

        current_date = datetime.datetime.now()
        last_month = current_date - datetime.timedelta(days=current_date.day)
        last_month_month = last_month.strftime("%Y-%m")[5:]
        # print(last_month_month)

    

        conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',password='123456',port=3306,charset='utf8')
        cursor = conn.cursor()

        #先更新一下test_name_list 以便找实验时方便调用 (分活儿时从test plan中的Data表格更新来的简化的实验名列表)的diff_score列和auditor列
        cursor.execute('''UPDATE test_name_list SET diff_score=(SELECT diff_score FROM testlist WHERE testlist.test_name 
        LIKE concat('%',(test_name_list.test_name),'%' ) AND testlist.test_day=1 ), auditor=(SELECT auditor FROM testlist WHERE testlist.test_name 
        LIKE concat('%',(test_name_list.test_name),'%' ) AND testlist.test_day=1 ) ''')
        conn.commit()



        #20241024新增：取指定人员上周完成的报告, 指定人员为audit_priority=1的
        today_list_specified=[] #上周实验，按选定的人（实验号+人名）
        today_list_specified_onlytestno=[] #只有实验号，没有人名
        today_test_specified=[] #上周实验，选定实验名
        auditor_test_names=[] #有auditor的实验名清单
        
        #取出有auditor的实验名清单
        cursor.execute('''SELECT test_name,auditor FROM test_name_list where auditor!='' ''')
        names=cursor.fetchall()
        for i in names:
            if i[0] not in auditor_test_names:           
                auditor_test_names.append(i[0]) 

        #下面是先查询名单，及相应的audit_priority,还有相应的sampling1
        #再根据名单及上述条件取实验

        cursor.execute('''select employee_name,category,testerlist.audit_priority,sampling1 from testerlist left join tester_audit_settings 
        on testerlist.audit_priority=tester_audit_settings.audit_priority''')
        specified_names=cursor.fetchall()
        for i in specified_names:
            print(i[0],i[1],i[2],i[3])
            name=i[0]
            sampling = int(i[3])        
            # 取报告条件：实验名有auditor,完成时间上周一到周日,且实验号不在第二个表中
            cursor.execute('''SELECT left(test_no,8) from assignment left join test_name_list on assignment.test_name=test_name_list.test_name 
            where operator=%s and ComDATE>=%s and ComDATE<=%s and test_day=total_day and assignment.test_name IN %s 
            order by diff_score desc,Test_hour* Coefficient DESC LIMIT %s  ''', (name,last_monday,last_sunday,auditor_test_names,sampling))   
            test_numbers=cursor.fetchall()
            for i in test_numbers:
                if i[0] is not None and i[0]+name not in today_list_specified: 
                    today_list_specified_onlytestno.append(i[0])  
                    today_list_specified.append(i[0]+name)  


        print('上周按人员抽样：',today_list_specified)
    
        #下面是先查询实验名，及相应的抽样上限sampling2
        #再根据名单及上述条件取实验
        cursor.execute('''select test_name,sampling2 from tester_audit_settings where test_name!='' ''')      
        specified_names=cursor.fetchall()
        for i in specified_names:
            test_name=i[0]        
            sampling = int(i[1])        
            # 取报告条件：完成时间上周一到周日,且实验号不在第二个表中
            cursor.execute('''SELECT test_no from assignment where test_name=%s and left(test_no,8) Not in %s and ComDATE>=%s and ComDATE<=%s and test_day=total_day 
            order by Test_hour*Coefficient DESC LIMIT %s  ''', (test_name,today_list_specified_onlytestno,last_monday,last_sunday,sampling))   
            test_numbers=cursor.fetchall()
            print(test_name,sampling,test_numbers)
            for i in test_numbers:
                if i[0] is not None and i[0]not in today_test_specified: 
                    
                    today_test_specified.append(i[0])  


        print('上周指定实验名抽样：',today_test_specified) 

        return(today_test_specified,today_list_specified)


    def auditlist():
        def querytest():
            # days=entry_days.get()  
            days='5'     

            #1.获取当前日期  
            todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
            today=datetime.datetime.now().strftime('%m%d')
            tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d')     
            pre_date1=(datetime.datetime.now()+datetime.timedelta(days=-60)).strftime('%Y-%m-%d')
            pre_date2=(datetime.datetime.now()+datetime.timedelta(days=-30)).strftime('%Y-%m-%d')
            # 格式化pre_days_upload, 为了比较大小
            Xdaysago = datetime.datetime.strptime(pre_date1,"%Y-%m-%d") 
          
            yesterdaycolumn=(datetime.datetime.now()+datetime.timedelta(days=-1)).strftime('%Y%m%d')  
            last_monday=(datetime.datetime.now() - datetime.timedelta(days=datetime.datetime.now().weekday() + 7)).strftime('%Y%m%d') 
            last_sunday=(datetime.datetime.now() - datetime.timedelta(days=datetime.datetime.now().isoweekday())).strftime('%Y%m%d') 


        
            #建立mysql的连接
            conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
                    password='123456',port=3306,charset='utf8')
            # 获得游标
            cursor = conn.cursor()
            # 取出两个名单列表备用
            diff_test_names=[]  #diff_score>1的实验清单
            no_auditor_names=[]  # 没有Auditor的实验清单，要避免取到

            #先更新一下test_name_list以便找实验时方便调用 (分活儿时从test plan中的Data表格更新来的简化的实验名列表)的diff_score列和auditor列
            cursor.execute('''UPDATE test_name_list SET diff_score=(SELECT diff_score FROM testlist WHERE testlist.test_name 
            LIKE concat('%',(test_name_list.test_name),'%' ) AND testlist.test_day=1 ), auditor=(SELECT auditor FROM testlist WHERE testlist.test_name 
            LIKE concat('%',(test_name_list.test_name),'%' ) AND testlist.test_day=1 ) ''')
            conn.commit()

            cursor.execute('''SELECT test_name FROM test_name_list where diff_score>1 ''')
            names=cursor.fetchall()
            for i in names:
                if i[0] not in diff_test_names:
                    diff_test_names.append(i[0])
            print(diff_test_names)
            cursor.execute('''SELECT test_name FROM test_name_list where auditor='' ''')
            names=cursor.fetchall()
            for i in names:
                if i[0] not in no_auditor_names:
                    no_auditor_names.append(i[0])
            #导出测试员过去60天内已经audit过的报告列表
            tester_audit_list=['WH123456']
            cursor.execute('''select report_no from tester_audit_reports where date>%s''',pre_date1)
            reports=cursor.fetchall()
            for i in reports:
                tester_audit_list.append(i[0])

            print("****60天")    
            print(tester_audit_list)



            n=10
            
            
 



            # filepath = r'C:\自动分活儿\Audit_tests_list since from '+pre_date1+'.xlsx'
            filepath = r'C:\自动分活儿\Audit_tests_list of last week '+today+'.xlsx'
            workbook = vb.Workbook(filepath) 
            workbook.save(filepath)
            workbook = vb.load_workbook(filepath)    


            # sheetname = 'Sincefrom'+pre_date1     
            # workbook.create_sheet(sheetname)
            # worksheet2 = workbook[sheetname]
            workbook.create_sheet('上周完成(指定实验)')
            worksheet3 = workbook['上周完成(指定实验)']

            workbook.create_sheet('上周完成(按人员等级)')
            worksheet4 = workbook['上周完成(按人员等级)']

            workbook.create_sheet('当前设定参数')
            worksheet5 = workbook['当前设定参数']

            fontstyle = vb.styles.Font(name='Times New Roman',bold=True,size=10)  #标题
            fontstyle1 = vb.styles.Font(name='Arial',bold=True,size=10)    #标题
            fontstyle2 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文蓝色
            alignstyle = vb.styles.Alignment(horizontal='center',vertical='center')  #居中  
            alignstyle1 = vb.styles.Alignment(horizontal='left',vertical='center')  #左 
            cellbackground= vb.styles.PatternFill(fill_type='lightGray') 



            worksheet3['A1'] = 'Product'  
            worksheet3['B1'] = 'Test_no'  
            worksheet3['C1'] = 'Qty'
            worksheet3['D1'] = 'Vintage'
            worksheet3['E1'] = 'Category'
            worksheet3['F1'] = 'Test_name'
            worksheet3['G1'] = 'Operator'
            worksheet3['H1'] = 'Engineer' 
            worksheet3['I1'] = 'Com_Date'
            worksheet3['J1'] = 'Audit_Date'
            worksheet3['K1'] = '是否ALT网站下载？'
            worksheet3['L1'] = 'Auditor'
            worksheet3['M1'] = 'Diff_score'
            worksheet3['N1'] = 'Score'
            worksheet3['O1'] = 'Issue Description'           
            worksheet3['P1'] = 'Root Cause Category'
            worksheet3['Q1'] = 'Actions'
            worksheet3['R1'] = 'Status'

            worksheet4['A1'] = 'Product'  
            worksheet4['B1'] = 'Test_no'  
            worksheet4['C1'] = 'Qty'
            worksheet4['D1'] = 'Vintage'
            worksheet4['E1'] = 'Category'
            worksheet4['F1'] = 'Test_name'
            worksheet4['G1'] = 'Operator'
            worksheet4['H1'] = 'Engineer' 
            worksheet4['I1'] = 'Com_Date'
            worksheet4['J1'] = 'Audit_Date'
            worksheet4['K1'] = '是否ALT网站下载？'
            worksheet4['L1'] = 'Auditor'
            worksheet4['M1'] = 'Diff_score'
            worksheet4['N1'] = 'Score'
            worksheet4['O1'] = 'Issue Description'           
            worksheet4['P1'] = 'Root Cause Category'
            worksheet4['Q1'] = 'Actions'
            worksheet4['R1'] = 'Status'

            worksheet5['A1'] = '姓名'  
            worksheet5['B1'] = 'Audit_priority'  
        
            worksheet5['D1'] = 'Audit_priority'
            worksheet5['E1'] = '最大抽样数量'
     
            worksheet5['G1'] = '当前指定实验'
            worksheet5['H1'] = '最大抽样数量' 
            #先把设定的表格信息写好
            cursor.execute('''select Employee_name, Audit_priority from testerlist ''')      
            names_priorities=cursor.fetchall()
            i=2
            for row in names_priorities:
                worksheet5.cell(i,1,value = row[0]).alignment = alignstyle 
                worksheet5.cell(i,1).alignment = alignstyle     
                worksheet5.cell(i,2,value = row[1]).alignment = alignstyle 
                worksheet5.cell(i,2).alignment = alignstyle 
                i=i+1

            cursor.execute('''select audit_priority,sampling1,test_name,sampling2 from tester_audit_settings ''')      
            specified_names=cursor.fetchall()     
            i=2
            for row in specified_names:
                if row[0]!='':
                    worksheet5.cell(i,4,value = row[0]).alignment = alignstyle 
                    worksheet5.cell(i,4).alignment = alignstyle   
                    worksheet5.cell(i,5,value = row[1]).alignment = alignstyle 
                    worksheet5.cell(i,5).alignment = alignstyle
                    i=i+1
            i=2
            for row in specified_names:       
                if row[2]!='':
                    worksheet5.cell(i,7,value = row[2]).alignment = alignstyle 
                    worksheet5.cell(i,7).alignment = alignstyle     
                    worksheet5.cell(i,8,value = row[3]).alignment = alignstyle 
                    worksheet5.cell(i,8).alignment = alignstyle
                    i=i+1                      





            for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1','Q1','R1']:
                # worksheet2[cell].fill = cellbackground
                # worksheet2[cell].alignment = alignstyle
                
                worksheet3[cell].fill = cellbackground
                worksheet3[cell].alignment = alignstyle

                worksheet4[cell].fill = cellbackground
                worksheet4[cell].alignment = alignstyle

                worksheet5[cell].fill = cellbackground
                worksheet5[cell].alignment = alignstyle

            # if int(days) < n and int(days)==120:
            if int(days) < n and int(days)!=0:
                number = int(days)       
                window_check_days.destroy()    
                print(int(days))    
                operator_names=[]         

                #读取数据库assignmnet所有内容(未分派实验不读)
                cursor.execute('''SELECT product,left(Test_no,8),Qty,Vintage, Test_category, Test_name,Operator, Lab_Engineer,Comdate,count(*) from assignment WHERE (vintage LIKE 'DVT2%%' or vintage LIKE 'PVT%%' or vintage LIKE 'ok%%' or vintage = '1stORT' or vintage = 'SA') 
                AND (Test_category LIKE 'A%%' or Test_category LIKE 'DVT%%' or Test_category = 'CALT' or Test_category = 'CDVT' or Test_category = 'Full ALT'  or Test_category = 'TMO') 
                AND (Test_name LIKE 'ATT%%' or Test_name LIKE 'TMO%%' or Test_name LIKE 'ALT%%' or Test_name LIKE 'EDu%%' or Test_name LIKE 'MDu%%' or Test_name LIKE 'EPr%%' or Test_name LIKE 'MXc%%') 
                and product!='' and Coefficient >'0.7' and Qty>'1' and Test_name !='Mdu11' and Test_name !='Mdu64' and Test_name !='Mdu80' and Test_name !='Mdu83' 
                and Test_name !='Mdu82' and Test_name not in %s and left(test_no,8) not in %s and test_day= total_day AND ComDate > %s AND ComDate < %s group by left(test_no,8) ORDER BY Test_hour* Coefficient DESC ''',(no_auditor_names,tester_audit_list,pre_date1,pre_date2))
                allrows=cursor.fetchall()  

                #看有多少个operator, 列表放入operator_names
                for i in allrows:
                    if i[6] not in operator_names:
                        operator_names.append(i[6])
                # print(operator_names)
                #定义一个大列表testlist，里面包含小列表，小列表的第0个元素是operator_name
                testlist=[]
                for name in operator_names:
                    testlist.append([name])
                #m是人名个数，也是testlist中包含的小列表个数
                m=len(testlist)
                #把allrows拿到的所有实验，按人名，分别放入以该人名为第0个元素的小列表中
                for list in testlist:
                    # print(list[0])                    
                    for i in allrows:
                        if i[6]==list[0] and i[1] not in list:
                            list.append(i[1])
                # print(testlist)

                #定义一个空列表，用于存放选出的实验号
                selectedlist=[]
                namelistselected=[]
                
                for i in range(1,9):  #这个数定义从每个人名下拿多少个实验，超过一定数值后就没差别了，拿的方法是从每个人先拿一个实验，不够再重新按人拿一遍
                    for list in testlist: #最早是把本行和上面一行颠倒的，即一直从一个人手里拿，再从另一个人手里拿多个，这样不行，会导致有些人被拿不到
                        if len(list) >=i+1 and len(selectedlist)<number:  #只要选的实验号不够120个，就可以一直拿。如果小列表中没有元素可拿，就不拿
                            selectedlist.append(list[i])
                            namelistselected.append(list[0])
                print('最终选出的实验号列表：',selectedlist)
                print('最终选出的实验号个数：',len(selectedlist),'总人数：',m)      
                print('被选的人：',namelistselected)    
                        
                

                i=2
                printlist=[]
                namelist=[]
        
                

                print(printlist)
                print(namelist)
                for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1','Q1','R1']:
                    # worksheet2[cell].font = fontstyle1
                    worksheet3[cell].font = fontstyle1   
                    worksheet4[cell].font = fontstyle1 


            


                worksheet3.column_dimensions['A'].width = 12
                worksheet3.column_dimensions['B'].width = 11
                worksheet3.column_dimensions['C'].width = 6
                worksheet3.column_dimensions['D'].width = 10
                worksheet3.column_dimensions['E'].width = 12
                worksheet3.column_dimensions['F'].width = 14
                worksheet3.column_dimensions['G'].width = 12
                worksheet3.column_dimensions['I'].width = 13
                worksheet3.column_dimensions['J'].width = 14
                worksheet3.column_dimensions['K'].width = 22
                worksheet3.column_dimensions['L'].width = 12
                worksheet3.column_dimensions['M'].width = 12
                worksheet3.column_dimensions['N'].width = 10
                worksheet3.column_dimensions['O'].width = 50
                worksheet3.column_dimensions['P'].width = 20
                worksheet3.column_dimensions['Q'].width = 50
                worksheet3.column_dimensions['R'].width = 15

                worksheet4.column_dimensions['A'].width = 12
                worksheet4.column_dimensions['B'].width = 11
                worksheet4.column_dimensions['C'].width = 6
                worksheet4.column_dimensions['D'].width = 10
                worksheet4.column_dimensions['E'].width = 12
                worksheet4.column_dimensions['F'].width = 14
                worksheet4.column_dimensions['G'].width = 12
                worksheet4.column_dimensions['I'].width = 13
                worksheet4.column_dimensions['J'].width = 14
                worksheet4.column_dimensions['K'].width = 22
                worksheet4.column_dimensions['L'].width = 12
                worksheet4.column_dimensions['M'].width = 12
                worksheet4.column_dimensions['N'].width = 10
                worksheet4.column_dimensions['O'].width = 50
                worksheet4.column_dimensions['P'].width = 20
                worksheet4.column_dimensions['Q'].width = 50
                worksheet4.column_dimensions['R'].width = 15

                worksheet5.column_dimensions['A'].width = 15
                worksheet5.column_dimensions['B'].width = 18
                worksheet5.column_dimensions['C'].width = 10
                worksheet5.column_dimensions['D'].width = 18
                worksheet5.column_dimensions['E'].width = 18
                worksheet5.column_dimensions['F'].width = 10
                worksheet5.column_dimensions['G'].width = 18
                worksheet5.column_dimensions['I'].width = 18
                worksheet5.column_dimensions['J'].width = 18
                worksheet5.column_dimensions['H'].width = 18
                # worksheet2.row_dimensions[1].height =20
                # worksheet2.auto_filter.ref = "A1:Q1"

                worksheet3.row_dimensions[1].height =20
                worksheet3.auto_filter.ref = "A1:R1"

                worksheet4.row_dimensions[1].height =20
                worksheet4.auto_filter.ref = "A1:R1"

                worksheet5.row_dimensions[1].height =20
                worksheet5.auto_filter.ref = "A1:R1"                

                #下面为导出上周报告  
                report_list=lastweek_report_export(ip_address)
                # print(report_list)
                auditor_test_names=[] #有auditor的实验名清单
                #取出有auditor的实验名清单
                cursor.execute('''SELECT test_name,auditor FROM test_name_list where auditor!='' ''')
                names=cursor.fetchall()
                for i in names:
                    if i[0] not in auditor_test_names:           
                        auditor_test_names.append(i[0])
                        
                i=2
                for number in report_list[0]:
                    # number=item[:8]
                    # name=item[8:]
                    # print(number,name)
                    #从assignment中抽出指定报告，且验证报告中实验名应该有auditor
                    cursor.execute('''SELECT product,test_no,Qty,Vintage, Test_category, Test_name,Operator, Lab_Engineer,Comdate from assignment
                     WHERE test_no= %s ''',number)

                    row=cursor.fetchone()                
                                
                    worksheet3.cell(i,1,value = row[0]).alignment = alignstyle 
                    worksheet3.cell(i,1).alignment = alignstyle1 
                    worksheet3.cell(i,2,value = row[1]).alignment = alignstyle 
                    worksheet3.cell(i,2).alignment = alignstyle1 
                    worksheet3.cell(i,3,value = row[2]).alignment = alignstyle     
                    worksheet3.cell(i,4,value = row[3]).alignment = alignstyle
                    worksheet3.cell(i,4).alignment = alignstyle1  
                    worksheet3.cell(i,5,value = row[4]).alignment = alignstyle 
                    worksheet3.cell(i,5).alignment = alignstyle1 
                    worksheet3.cell(i,6,value = row[5]).alignment = alignstyle  
                    test_name=row[5]       
                    worksheet3.cell(i,7,value = row[6]).alignment = alignstyle 
                    worksheet3.cell(i,7).font = fontstyle2
                    worksheet3.cell(i,8,value = row[7]).alignment = alignstyle                     
                    worksheet3.cell(i,9,value = row[8]).alignment = alignstyle 
                    cursor.execute('''select auditor, diff_score from test_name_list where test_name=%s''',test_name)
                    infor=cursor.fetchone()
                    worksheet3.cell(i,12,value = infor[0]).alignment = alignstyle                     
                    worksheet3.cell(i,13,value = infor[1]).alignment = alignstyle 
                    i=i+1

                #下面为导出指定人员上周报告  

                i=2
                for item in report_list[1]:
                    number=item[:8]
                    name=item[8:]
                    # print(number,name)
                    #从assignment中抽出指定报告，且验证报告中实验名应该有auditor
                    cursor.execute('''SELECT product,left(Test_no,8),Qty,Vintage, Test_category, Test_name,Operator, Lab_Engineer,Comdate from assignment
                     WHERE operator=%s and test_no like %s and test_name in %s ''',(name,(number+'%'),auditor_test_names))
                    row=cursor.fetchone()                
                                
                    worksheet4.cell(i,1,value = row[0]).alignment = alignstyle 
                    worksheet4.cell(i,1).alignment = alignstyle1 
                    worksheet4.cell(i,2,value = row[1]).alignment = alignstyle 
                    worksheet4.cell(i,2).alignment = alignstyle1 
                    worksheet4.cell(i,3,value = row[2]).alignment = alignstyle     
                    worksheet4.cell(i,4,value = row[3]).alignment = alignstyle
                    worksheet4.cell(i,4).alignment = alignstyle1  
                    worksheet4.cell(i,5,value = row[4]).alignment = alignstyle 
                    worksheet4.cell(i,5).alignment = alignstyle1 
                    worksheet4.cell(i,6,value = row[5]).alignment = alignstyle  
                    test_name=row[5]       
                    worksheet4.cell(i,7,value = row[6]).alignment = alignstyle 
                    worksheet4.cell(i,7).font = fontstyle2
                    worksheet4.cell(i,8,value = row[7]).alignment = alignstyle                     
                    worksheet4.cell(i,9,value = row[8]).alignment = alignstyle 
                    cursor.execute('''select auditor, diff_score from test_name_list where test_name=%s''',test_name)
                    infor=cursor.fetchone()
                    worksheet4.cell(i,12,value = infor[0]).alignment = alignstyle                     
                    worksheet4.cell(i,13,value = infor[1]).alignment = alignstyle 
                    i=i+1


                workbook.remove(workbook['Sheet'])
                workbook.active=worksheet3
                workbook.save(filepath)
                notice='报告已输出到自动分活儿文件夹中！'
                print(notice) 
                tkinter.messagebox.showinfo(title='Hi', message='已输出Audit test list表格数据到自动分活儿文件夹中！')
            else:       
                msg= '可导出实验数量不满足要求！请确保数量不等于0且小于：',n    
                tkinter.messagebox.showerror(title='Hi', message=msg)
                window_check_days.destroy()

        window_check_days=tk.Toplevel(window)
        # window_check_days.geometry('350x160')
        window_check_days.geometry('450x160')
        window_check_days.title('Audit tet list数据导出')


        days_enter=tk.StringVar()
        # tk.Label(window_check_days,text='* 请输入需要导出的完成时间在过去30~60天的实验数量:',bg='yellow',font=('Arial',9)).place(x=30,y=10)
        tk.Label(window_check_days,text='* 1.上周完成（按人员等级）表中按人员优先级抽取上周完成实验！', font=('Arial',9)).place(x=30,y=10)
        tk.Label(window_check_days,text='* 2.上周完成（指定实验）表格抽取上述表格未包含的指定实验！', font=('Arial',9)).place(x=30,y=30)    
        # entry_days=tk.Entry(window_check_days,textvariable=days_enter,show=None)
        # entry_days.place(x=100,y=40)  
        tk.Label(window_check_days,text='* 表1中人员Audit_priority分为1,2,3,4。每级最大抽样实验数量可设置！',bg='yellow',font=('Arial',9)).place(x=30,y=70)
        tk.Label(window_check_days,text='* 表2中指定实验清单及抽取个数可以设置！',bg='yellow',font=('Arial',9)).place(x=30,y=90)

        btn_confirm_days=tk.Button(window_check_days,text='确定',width=9,height=1, command=querytest)
        btn_confirm_days.place(x=180,y=120)

    def exit(): 
 
        import subprocess

        PIDlist=[]
        PIDlist1=[]
        result = os.popen("tasklist")  #读取所有的进程

        for line in result.readlines():  #对每一条进程进行遍历，按行读取
            if current_ver_name in line:
                print(line)
                # IDnumber=re.findall('\d{5}',line)   #re提取的内容是一个列表,本句提取固定的5个数字
                IDnumber=re.findall(r'(\d+) Console', line)   #从每一个进程中匹配进程编号PID
                if IDnumber !=[]:
                    print(IDnumber[0])   
                    PIDlist.append(IDnumber[0])    #把进程编号加入列表
        # print('PIDlist:',PIDlist)
        if len(PIDlist)>2:  #进程数量大于2个时才进行后续的杀进程，杀掉最后两个进程之前的所有进程    
            m=(len(PIDlist))-2
            # print(len(PIDlist),m)
            for n in PIDlist[:m]: #Kill最后两个进程之前的所有进程
                # print(n)
                subprocess.Popen("taskkill /F /PID %d" %int(n), shell=True)    #按PID杀进程, 用os.system会闪屏

        #终止上一版残留进程, 每次升级可以把上一版名称的编号改在这里V1.7
        for line in result.readlines():
            if last_ver_name in line:
                IDnumber=re.findall(r'(\d+) Console', line)
                if IDnumber !=[]:
                    PIDlist1.append(IDnumber[0])
            if 'GTS_Infor_Query' in line:  #如果有之前运行的GTS进程，全部关闭
                print(line)
                IDnumber=re.findall(r'(\d+) Console', line)
                if IDnumber !=[]:
                    PIDlist1.append(IDnumber[0])
        # print('PIDlist1:',PIDlist1)
        for i in PIDlist1: #Kill最后两个进程之前的所有进程
            subprocess.Popen("taskkill /F /PID %d" %int(i), shell=True) 
 
        path = os.path.join(os.path.expanduser("~"), 'Desktop')  #获取桌面的路径       
        for file in os.listdir(path):   
            if last_ver_name in file:        
                pathfile=path+'\\'+file 
                os.remove(pathfile)

        sys.exit()   
        

    def help():
      help1='           ------------------使用须知/更新说明：---------------------'      
      help2='1.Submit时Category,Technician,Engineer,Description,Score为必填项。'
      help3='2.Display显示的是最后的15条记录（且为30天内提交的）。'
      help4='3.Display出来的内容可以逐条修改，然后重新Submit。'
      help5='4.Display后, 去掉某条记录中的Technician, 再Submit则会删除该条记录。'     
      help6='5.每次新版本发布后，之前版本将无法继续使用。'
      help7='V1.7更新: 下拉菜单改成只能选择而不能输入和编辑。'
      help8='V1.8更新: 解决偶尔闪屏的问题。'
      help9='V2.2更新: 添加Audit report 导出功能。'
      help10='V2.4更新: 增添加分时邮件通知功能。'
      help11='V2.5更新: 报告时间更新。add email notification after award input'
      help12='optimize audit list export, add engineer list'
      help=help1+'\n'+'\n'+help2+'\n'+help3+'\n'+help4+'\n'+help5+'\n'+help6+'\n'+help7+'\n'+help8+'\n'+help9+'\n'+help10+'\n'+help11+'\n'+help12+'\n'
      tkinter.messagebox.showinfo(title='Help', message=help)





    button0=tkinter.Button(window,font=('Arial', 9,'bold'),text='New',width=9,height=1,command=new,bg='#A0A0A0', fg='#ffff00')
    button1=tkinter.Button(window,font=('Arial', 9,'bold'),text='Display',width=9,height=1,command=display,bg='#A0A0A0', fg='#ffff00') 
    button2=tkinter.Button(window,font=('Arial', 9,'bold'),text='Submit',width=9,height=1,command=update,bg='#A0A0A0', fg='#ffff00')
    
    button3=tkinter.Button(window,font=('Arial', 9,'bold'),text='Exit',width=9,height=1,command=exit,bg='#A0A0A0', fg='#ffff00').grid(row=16,column=7)  
    button4=tkinter.Button(window,text='Export',width=9,height=1,command=export,bg='#A0A0A0', fg='red')   
    button5=tkinter.Button(window,font=('Arial', 9,'bold'),text='Help',width=9,height=1,command=help,bg='#A0A0A0', fg='#ffff00').grid(row=16,column=8) 
    button6=tkinter.Button(window,text='Auditlist',width=9,height=1,command=auditlist,bg='#A0A0A0', fg='red')   
    
    balloon=Label(window)    #先定义一个显示气球


    #------以下20231126新增显示菜单

    def data_collection():  
        # 
        print('** data_collection：运行数据收集之前请先在考试系统执行一次数据导出操作！确保数据最新！')
        #1.获取当前日期  
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
        today=datetime.datetime.now().strftime('%m%d')
        tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d')     
        pre_days_upload=(datetime.datetime.now()+datetime.timedelta(days=-180)).strftime('%Y-%m-%d')
        # 格式化pre_days_upload, 为了比较大小
        Xdaysago = datetime.datetime.strptime(pre_days_upload,"%Y-%m-%d") 
        display_days=30  #显示一个月内的输入记录，但是要限制显示15条
        pre_date=(datetime.datetime.now()+datetime.timedelta(days=-display_days)).strftime('%Y-%m-%d') 

        #首先将tester_capability中的测试能力考试及实际能力数据复制到tester_performance_summary表中，同时进行得分计算
        # tester_performance_summary表中名单需要手工增加或删除
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
        password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor() 
 

        cursor.execute('''select name, exam_score,capability_score,overall_score,performance_score,datetime from tester_capability where DATETIME=(select max(DATETIME) FROM tester_capability)''')
        capability_result=cursor.fetchall()
        for i in capability_result:
 
            cursor.execute('''update tester_performance_summary set exam_score=%s,capability_score=%s,overall_capability_score=%s,capability_score_time=%s where name=%s''',(i[1],i[2],i[4],i[5],i[0]))
            # 董胜的能力分值设为20分，其它人按实际来
            cursor.execute('''update tester_performance_summary set overall_capability_score='20',capability_score_time=%s where name='董胜' ''',i[5])
        conn.commit()
        conn.close()
        print('1.获取综合能力分值并更新到tester_performance_summary!')


        # 同时连接2个数据库，把实验工作量数据取出并添加到tester_performance_summary表格中，同时计算得分
        source_db = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',password='123456',port=3306,charset='utf8')
        dest_db = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')  
        
        resigned_name_list=('陈义','黄溶','李金波','王路','黎阿敏','袁丹','刘谊','柳心怡','陈书恒','李泽辉','谭露','王海轮','王畅畅')   #做为一个参数放进语句中不行，不知道何故？
        # 统计工作量时的起始日期，每年要改一次
        start_date='2024-04-01'
        # 执行查询语句
        with source_db.cursor() as cursor:
            cursor.execute('''select operator, sum(a.test_hour*a.Coefficient*t.Diff_score),sum(a.test_hour*a.Coefficient) from 
    assignment as a LEFT JOIN testlist as t on t.test_nameid = a.test_nameid and t.Test_day=1 where a.operator !='' and a.operator not in %s and a.test_hour !=0 and
    a.date> %s and a.test_hour>0 and t.Diff_score>=1 group by operator order by sum(a.test_hour*a.Coefficient*t.Diff_score) desc''',(resigned_name_list,start_date))
            workload_data = cursor.fetchall()
        # 将数据复制到目标数据库
        with dest_db.cursor() as cursor:    
            for i in workload_data:    
                cursor.execute('update tester_performance_summary set workload=%s where name=%s', (math.ceil(i[1]),i[0]))
            dest_db.commit()

            cursor.execute('select max(workload) from tester_performance_summary')
            maxworkload=cursor.fetchone()[0]
            print('....实验员工作量最大值：',maxworkload)

            cursor.execute('''update tester_performance_summary set workload_score=40 where workload>=%s*0.9 ''',maxworkload)
            cursor.execute('''update tester_performance_summary set workload_score=20 where workload<=%s*0.5''',maxworkload)
            cursor.execute('''update tester_performance_summary set workload_score=(20+20*(workload-%s*0.5)/(%s*(0.9-0.5))) where workload>%s*0.5 and workload<%s*0.9''',(maxworkload,maxworkload,maxworkload,maxworkload))
        
 
        dest_db.commit()
        source_db.close()
        dest_db.close()
        print('2.把实验员实验工作量数据从assignment中取出添加到tester_performance_summary表格中，同时计算工作量的绩效分！')
        
 
        # 同时连接2个数据库，把TQM实验工作量数据取出并添加到tester_performance_summary表格中，同时计算得分
        source_db = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',password='123456',port=3306,charset='utf8')
        dest_db = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')  
        
        TQM_name_list=('赵俊凯','汪焕','陈凯','熊春强')   #做为一个参数放进语句中不行，不知道何故？

        # 执行查询语句
        with source_db.cursor() as cursor:
            cursor.execute('''select lab_engineer, sum(test_hour*coefficient) from assignment where lab_engineer in %s and DATE >=%s GROUP BY lab_engineer ORDER BY SUM(test_hour*coefficient) desc''',(TQM_name_list,start_date))
            workload_data = cursor.fetchall()
        # 将数据复制到目标数据库
        with dest_db.cursor() as cursor:    
            for i in workload_data:    
                cursor.execute('update tester_performance_summary set workload=%s where name=%s', (math.ceil(i[1]),i[0]))
            dest_db.commit()

            cursor.execute('''select max(workload),name from tester_performance_summary where performance_category='TQM' ''')
            maxworkload=cursor.fetchone()[0]
            print('....TQM工作量最大值：',maxworkload)  
            workload_upperlimit=maxworkload*0.9
            workload_lowerlimit=maxworkload*0.6  
            x=maxworkload*(0.9-0.6) 

            #TQM 最低35分
            cursor.execute('''update tester_performance_summary set workload_score=40 where workload>=%s and performance_category='TQM' ''',workload_upperlimit)
            cursor.execute('''update tester_performance_summary set workload_score=35 where workload<=%s and performance_category='TQM' ''',workload_lowerlimit)
            cursor.execute('''update tester_performance_summary set workload_score=(35+10*(workload-%s)/%s) where workload>%s and workload<%s and performance_category='TQM' ''',(workload_lowerlimit,x,workload_lowerlimit,workload_upperlimit))
            #非TQM，非Tester，非on-site 自动置为40分 
            cursor.execute('''update tester_performance_summary set workload_score=40 where performance_category!='TQM' and performance_category!='Tester' and performance_category!='On-site' ''')
        
        #提交更改并关闭连接
        dest_db.commit()
        source_db.close()
        dest_db.close()
        print('3.把TQM实验工作量数据从assignment中取出添加到tester_performance_summary表格中，同时计算绩效分！')

        # 连接数据库，把Audit/award数据取出并添加到tester_performance_summary表格中,该数据直接就是得分
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')  

        # 执行查询语句
        cursor=conn.cursor()
        cursor.execute('''select technician, SUM(score) from auditrecord where (score=1 or score=-1) and date>=%s GROUP BY technician''',start_date)
        score_data = cursor.fetchall()
        # 将数据复制到目标表格
        special_name_list1=['王锦','董胜','李紫华','张玉','彭彩虹']
        special_name_list2=['陈凯','汪焕','熊春强','赵俊凯']
        for i in score_data:    
            if i[1]>20:
                i1=20
            elif i[1]<-20:
                i1=-20
            else:
                i1=i[1]
            if i[0] in special_name_list1:  #Special list1统一调到15分
                i_s1=15
                cursor.execute('''update tester_performance_summary set award_audit_score=%s where name=%s''', (i_s1,i[0]))
                i_s1=0
            # elif i[0] in special_name_list2: #Special list2 统一加5分,上限20
            #     i_s2=i1+5
            #     if i_s2>20:
            #         i_s2=20                
            #     cursor.execute('''update tester_performance_summary set award_audit_score=%s where name=%s''', (i_s2,i[0]))
            #     i_s2=0
            else:
                cursor.execute('update tester_performance_summary set award_audit_score=%s where name=%s', (i1,i[0]))
            # cursor.execute('update tester_performance_summary set award_audit_score=%s where name=%s', (i1,i[0]))
        conn.commit()
 

        print('4.把Audit/award数据取出并添加到tester_performance_summary表格中,该数据直接就是得分，不超过+/-20!')
     
 
        #Tester,o-site,TQM：people_score_summary=所有人打分和，包括我，overall_perfroamce_score= 各种分值总和
        cursor.execute('''update tester_performance_summary set people_score_summary=nizhao_score+zihua_score+zhangyu_score+wangjin_score+lijun_score,overall_performance_score=workload_score + overall_capability_score + extra_responsibility_score + award_audit_score + nizhao_score+zihua_score+zhangyu_score+wangjin_score+lijun_score ''')
      
        conn.commit()
        conn.close()
        print('5.基于不同人员类别，计算一下people_score_summary 及最终总分')
        tk.messagebox.showwarning('提示',"数据收集完成!") 

    def score_sheet_download():
        tk.messagebox.showwarning('提示',"1.打分表将被下载到桌面，除打分和评语外不要对表格做其它更改；  2.在打分表中打分并保存，文件位置放桌面，然后点击“打分上传”即可；   3.管理员锁定打分功能前可重复上传更新，以最后一次上传数据为准；") 

        #获取用户名
        global user_name
        user_name = getpass.getuser()  
        #待获取名单
        global namelist
        namelist=['lijun24','nizhao2','wangjin24','zhangyu90','lizh48'] #定义可以下载打分表的电脑
        #建立mysql的连接
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor()

        path = os.path.join(os.path.expanduser("~"), 'Desktop')  #获取桌面的路径
        pathfile = path+'\Score_sheet.xlsx'
        workbook = vb.Workbook(pathfile)
        workbook.save(pathfile)
        workbook=vb.load_workbook(pathfile)
        workbook.create_sheet(user_name)
        workbook.remove(workbook['Sheet'])
        workbook.save(pathfile)
        worksheet1=workbook[user_name]

        fontstyle = vb.styles.Font(name='Times New Roman',bold=True,size=10)  #标题
        fontstyle1 = vb.styles.Font(name='Arial',bold=True,size=10)    #标题
        fontstyle2 = vb.styles.Font(name='Arial',bold=True,size=10,color='0000FF')  #蓝色
        fontstyle3 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文兰色
        fontstyle4 = vb.styles.Font(name='Arial',size=10,color='DC143C')  #正文红色
        alignstyle = vb.styles.Alignment(horizontal='left',vertical='center')  #居中
        alignstyle1 = vb.styles.Alignment(horizontal='center',vertical='center')  #居中
        cellbackground= vb.styles.PatternFill(fill_type='lightGray') 
        cellbackground2= vb.styles.PatternFill(fill_type='gray125') 

        if user_name=='lijun24':  
            #下载表格前先汇总一下，把其它人的加分汇总一下，特殊岗位的人的分值重新分派一下。
            data_collection()

            cursor.execute('''select count(*) from tester_performance_summary ''')
            b=cursor.fetchone()  
            #单元格设置固定选择项
            data_validation=DataValidation(type='list',formula1='"-10,-9,-8,-7,-6,-5,-4,-3,-2,-1,0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15"')
            worksheet1.add_data_validation(data_validation)
            #提示内容
            data_validation.promptTitle = '打分范围：'
            data_validation.prompt = '-10 ~ 15分'
            #错误提示
            data_validation.errorTitle = '无效打分'
            data_validation.error = '打分范围应为-10~15之间整数'
            
            worksheet1['A1'] = '姓名'  
            worksheet1['B1'] = '岗位类别'  
            worksheet1['C1'] = '工作量'
            worksheet1['D1'] = '工作量得分'
            worksheet1['E1'] = '考试分值'
            worksheet1['F1'] = '测试能力'
            worksheet1['G1'] = '综合能力'
            worksheet1['H1'] = '额外职责'
            worksheet1['I1'] = 'Award/Audit'
            worksheet1['J1'] = '已打分值'
            worksheet1['K1'] = '王锦打分'   
            worksheet1['L1'] = '王锦评语' 
            worksheet1['M1'] = '紫华打分'
            worksheet1['N1'] = '紫华评语'
            worksheet1['O1'] = '张玉打分'
            worksheet1['P1'] = '张玉评语'   
            worksheet1['Q1'] = '倪昭打分'     
            worksheet1['R1'] = '倪昭评语' 
            worksheet1['S1'] = '李军打分'
            worksheet1['T1'] = '李军评语'
            worksheet1['U1'] = '绩效总分'        

            cursor.execute('''select name,performance_category,workload,workload_score,exam_score,capability_score,overall_capability_score,
            extra_responsibility_score,award_audit_score,people_score_summary,wangjin_score,wangjin_comments,zihua_score,zihua_comments,
            zhangyu_score,zhangyu_comments,nizhao_score,nizhao_comments,lijun_score,lijun_comments,overall_performance_score from tester_performance_summary order by overall_performance_score desc ''')
            allrows=cursor.fetchall()  
            
            for row in range(2,b[0]+2):      
                worksheet1.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
                worksheet1.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle 
                worksheet1.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle 
                worksheet1.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle 
                worksheet1.cell(row,4,value = allrows[row-2][3]).font = fontstyle3 
                worksheet1.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle 
                worksheet1.cell(row,6,value = allrows[row-2][5]).alignment = alignstyle             
                worksheet1.cell(row,7,value = allrows[row-2][6]).alignment = alignstyle 
                worksheet1.cell(row,7,value = allrows[row-2][6]).font = fontstyle3 
                worksheet1.cell(row,8,value = allrows[row-2][7]).alignment = alignstyle 
                worksheet1.cell(row,8,value = allrows[row-2][7]).font = fontstyle3 
                worksheet1.cell(row,9,value = allrows[row-2][8]).alignment = alignstyle 
                worksheet1.cell(row,9,value = allrows[row-2][8]).font = fontstyle3 
                worksheet1.cell(row,10,value = allrows[row-2][9]).alignment = alignstyle 
                worksheet1.cell(row,10,value = allrows[row-2][9]).font = fontstyle3             
                worksheet1.cell(row,11,value = allrows[row-2][10]).alignment = alignstyle1                   
                worksheet1.cell(row,12,value = allrows[row-2][11]).alignment = alignstyle 
                worksheet1.cell(row,13,value = allrows[row-2][12]).alignment = alignstyle1       
                worksheet1.cell(row,14,value = allrows[row-2][13]).alignment = alignstyle 
                worksheet1.cell(row,15,value = allrows[row-2][14]).alignment = alignstyle1         
                worksheet1.cell(row,16,value = allrows[row-2][15]).alignment = alignstyle             
                worksheet1.cell(row,17,value = allrows[row-2][16]).alignment = alignstyle1      
                worksheet1.cell(row,18,value = allrows[row-2][17]).alignment = alignstyle 
                worksheet1.cell(row,19,value = allrows[row-2][18]).alignment = alignstyle1       

                data_validation.add(worksheet1.cell(row,19))
                worksheet1.cell(row,20,value = allrows[row-2][19]).alignment = alignstyle 
                worksheet1.cell(row,21,value = allrows[row-2][20]).alignment = alignstyle1 
                worksheet1.cell(row,21,value = allrows[row-2][20]).font = fontstyle3 

            for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1','Q1','R1','S1','T1','U1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle1
                worksheet1[cell].alignment = alignstyle
            for cell in ['D1','G1','H1','I1','U1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle2
                worksheet1[cell].alignment = alignstyle
            #对auditor,equipment,samples,leader行进行阴影填充        
            for i in range(1,80):
                category=worksheet1.cell(row = i, column =2).value
                if category =='Auditor' or category =='Leader' or category =='Samples' or category =='Equipment' or category =='TQM':
                    for j in range(1,22):
                        worksheet1.cell(row = i, column =j).fill=cellbackground2


            worksheet1.column_dimensions['A'].width = 10
            worksheet1.column_dimensions['B'].width = 10
            worksheet1.column_dimensions['C'].width = 8
            worksheet1.column_dimensions['D'].width = 12
            worksheet1.column_dimensions['E'].width = 10
            worksheet1.column_dimensions['F'].width = 10
            worksheet1.column_dimensions['G'].width = 10
            worksheet1.column_dimensions['H'].width = 10
            worksheet1.column_dimensions['I'].width = 10
            worksheet1.column_dimensions['J'].width = 10
            worksheet1.column_dimensions['K'].width = 10
            worksheet1.column_dimensions['L'].width = 12
            worksheet1.column_dimensions['M'].width = 10
            worksheet1.column_dimensions['N'].width = 12
            worksheet1.column_dimensions['O'].width = 10
            worksheet1.column_dimensions['P'].width = 12
            worksheet1.column_dimensions['Q'].width = 10        
            worksheet1.column_dimensions['R'].width = 12
            worksheet1.column_dimensions['S'].width = 10
            worksheet1.column_dimensions['T'].width = 44
            worksheet1.column_dimensions['U'].width = 10             

            worksheet1.row_dimensions[1].height =20
            worksheet1.auto_filter.ref = "A1:R1"      

            workbook.save(pathfile)  
            print(user_name,'打分表格下载成功！')
            tkinter.messagebox.showinfo(title='Hi', message=user_name +' 打分表格下载成功！')

        elif user_name=='wangjin24':  
            cursor.execute('''select count(*) from tester_performance_summary where name!='王锦' ''')
            b=cursor.fetchone()  
            #单元格设置固定选择项
            data_validation=DataValidation(type='list',formula1='"1,2,3,4,5,6,7,8,9,10"')
            worksheet1.add_data_validation(data_validation)
            #提示内容
            data_validation.promptTitle = '打分范围：'
            data_validation.prompt = '1 ~ 10分, 均值要<=6'
            #错误提示
            data_validation.errorTitle = '无效打分'
            data_validation.error = '打分范围应为1 ~ 10之间整数'


            #单元格设置固定选择项
            data_validation1=DataValidation(type='list',formula1='"0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20"',showDropDown=True)
            worksheet1.add_data_validation(data_validation1)
            #提示内容
            data_validation1.promptTitle = '打分范围：'
            data_validation1.prompt = '0 ~ 20分'
            #错误提示
            data_validation1.errorTitle = '无效打分'
            data_validation1.error = '打分范围应为0~20之间整数'

            worksheet1['A1'] = '姓名'  
            worksheet1['B1'] = '人员类别'  
            worksheet1['C1'] = '工作量'
            worksheet1['D1'] = '工作量得分'
            worksheet1['E1'] = '考试分值'
            worksheet1['F1'] = '测试能力'
            worksheet1['G1'] = '综合能力'
            worksheet1['H1'] = 'Award/Audit'
            worksheet1['I1'] = '额外职责分'

            worksheet1['J1'] = '王锦打分'
            worksheet1['K1'] = '王锦评语'    
        

        

            cursor.execute('''select name,performance_category,workload,workload_score,exam_score,capability_score,overall_capability_score,
            award_audit_score,extra_responsibility_score,wangjin_score,wangjin_comments from tester_performance_summary where name!='王锦' ''')
            allrows=cursor.fetchall()  
            
            for row in range(2,b[0]+2):      
                worksheet1.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
                worksheet1.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle 
                worksheet1.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle 
                worksheet1.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle 
                # worksheet1.cell(row,4,value = allrows[row-2][3]).font = fontstyle3 
                worksheet1.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle 
                worksheet1.cell(row,6,value = allrows[row-2][5]).alignment = alignstyle             
                worksheet1.cell(row,7,value = allrows[row-2][6]).alignment = alignstyle 
                # worksheet1.cell(row,7,value = allrows[row-2][6]).font = fontstyle3 
                worksheet1.cell(row,8,value = allrows[row-2][7]).alignment = alignstyle 
                # worksheet1.cell(row,8,value = allrows[row-2][7]).font = fontstyle3   
                # worksheet1.cell(row,8).fill = cellbackground            
                # data_validation1.add(worksheet1.cell(row,8))  

                worksheet1.cell(row,9,value = allrows[row-2][8]).alignment = alignstyle 
                worksheet1.cell(row,9,value = allrows[row-2][8]).font = fontstyle3    
                # worksheet1.cell(row,9).fill = cellbackground            
                data_validation1.add(worksheet1.cell(row,9))                  

                worksheet1.cell(row,10,value = allrows[row-2][9]).alignment = alignstyle1 
                worksheet1.cell(row,10,value = allrows[row-2][9]).font = fontstyle3  
                worksheet1.cell(row,10).fill = cellbackground 
                data_validation.add(worksheet1.cell(row,10)) 
                worksheet1.cell(row,11,value = allrows[row-2][10]).alignment = alignstyle  

            #加一个备注说明：
            comments='说明：'
            comments1='1. 只能更新 “额外职责分”，“王锦打分”，“王锦评语” 三列，其它列数据供参考。'
            comments2='2. 上传前请计算一下打分列均值，不要超过6.0分，否则会从最高分删减。'
            comments3='3.  “额外职责分”，“王锦打分” 两列目前显示的是系统自动填写的默认分值，可以在此基础上进一步修改。'
            worksheet1.cell(2,13,value = comments).font= fontstyle
            worksheet1.cell(3,13,value = comments1).alignment = alignstyle
            worksheet1.cell(4,13,value = comments2).alignment = alignstyle
            worksheet1.cell(5,13,value = comments3).alignment = alignstyle
        

            for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle1
                worksheet1[cell].alignment = alignstyle
            for cell in ['I1','J1','K1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle2
                worksheet1[cell].alignment = alignstyle

            worksheet1.column_dimensions['A'].width = 10
            worksheet1.column_dimensions['B'].width = 10
            worksheet1.column_dimensions['C'].width = 8
            worksheet1.column_dimensions['D'].width = 12
            worksheet1.column_dimensions['E'].width = 10
            worksheet1.column_dimensions['F'].width = 10
            worksheet1.column_dimensions['G'].width = 10
            worksheet1.column_dimensions['H'].width = 10
            worksheet1.column_dimensions['I'].width = 14
            worksheet1.column_dimensions['J'].width = 10
            worksheet1.column_dimensions['K'].width = 40
        
            

            worksheet1.row_dimensions[1].height =20
            worksheet1.auto_filter.ref = "A1:J1"      

            workbook.save(pathfile)  
            print(user_name,'打分表格下载成功！')
            tkinter.messagebox.showinfo(title='Hi', message=user_name +' 打分表格下载成功！')

        elif user_name=='nizhao2':  
            cursor.execute('''select count(*) from tester_performance_summary ''')
            b=cursor.fetchone()  
            #单元格设置固定选择项
            data_validation=DataValidation(type='list',formula1='"1,2,3,4,5,6,7,8,9,10"')
            worksheet1.add_data_validation(data_validation)
            #提示内容
            data_validation.promptTitle = '打分范围：'
            data_validation.prompt = '1 ~ 10分, 均值要<=6'
            #错误提示
            data_validation.errorTitle = '无效打分'
            data_validation.error = '打分范围应为1~10之间整数'

            worksheet1['A1'] = '姓名'  
            worksheet1['B1'] = '人员类别'  
            worksheet1['C1'] = '工作量'
            worksheet1['D1'] = '工作量得分'
            worksheet1['E1'] = '考试分值'
            worksheet1['F1'] = '测试能力'
            worksheet1['G1'] = '综合能力'
            worksheet1['H1'] = '额外职责'
            worksheet1['I1'] = 'Award/Audit'
            worksheet1['J1'] = '倪昭打分' 
            worksheet1['K1'] = '倪昭评语'    
        

        

            cursor.execute('''select name,performance_category,workload,workload_score,exam_score,capability_score,overall_capability_score,
            extra_responsibility_score,award_audit_score,nizhao_score,nizhao_comments from tester_performance_summary ''')
            allrows=cursor.fetchall()  
            
            for row in range(2,b[0]+2):      
                worksheet1.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
                worksheet1.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle 
                worksheet1.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle 
                worksheet1.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle 
                # worksheet1.cell(row,4,value = allrows[row-2][3]).font = fontstyle3 
                worksheet1.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle 
                worksheet1.cell(row,6,value = allrows[row-2][5]).alignment = alignstyle             
                worksheet1.cell(row,7,value = allrows[row-2][6]).alignment = alignstyle 
                # worksheet1.cell(row,7,value = allrows[row-2][6]).font = fontstyle3 
                worksheet1.cell(row,8,value = allrows[row-2][7]).alignment = alignstyle 
                # worksheet1.cell(row,8,value = allrows[row-2][7]).font = fontstyle3   
                worksheet1.cell(row,9,value = allrows[row-2][8]).alignment = alignstyle 
                # worksheet1.cell(row,9,value = allrows[row-2][8]).font = fontstyle3           
                worksheet1.cell(row,10,value = allrows[row-2][9]).alignment = alignstyle1 
                worksheet1.cell(row,10,value = allrows[row-2][9]).font = fontstyle3 
                # worksheet1.cell(row,10).fill = cellbackground 
                data_validation.add(worksheet1.cell(row,10))   

                worksheet1.cell(row,11,value = allrows[row-2][10]).alignment = alignstyle 
                worksheet1.cell(row,11,value = allrows[row-2][10]).font = fontstyle3 
            #加一个备注说明：
            comments='说明：'
            comments1='1. 只更新 “倪昭打分”，“倪昭评语” 二列，其它列为系统自动收集数据，供参考'
            comments2='2. 上传前请计算一下打分列均值，不要超过6.0分，否则会从最高分删减'
            comments3='3. 目前系统已默认填写的分值可以根据实际情况修改'
            worksheet1.cell(2,12,value = comments).font= fontstyle
            worksheet1.cell(3,12,value = comments1).alignment = alignstyle
            worksheet1.cell(4,12,value = comments2).alignment = alignstyle
            worksheet1.cell(5,12,value = comments3).alignment = alignstyle        

            for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle1
                worksheet1[cell].alignment = alignstyle
            for cell in ['J1','K1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle2
                worksheet1[cell].alignment = alignstyle

            worksheet1.column_dimensions['A'].width = 10
            worksheet1.column_dimensions['B'].width = 10
            worksheet1.column_dimensions['C'].width = 8
            worksheet1.column_dimensions['D'].width = 12
            worksheet1.column_dimensions['E'].width = 10
            worksheet1.column_dimensions['F'].width = 10
            worksheet1.column_dimensions['G'].width = 10
            worksheet1.column_dimensions['H'].width = 10
            worksheet1.column_dimensions['I'].width = 14
            worksheet1.column_dimensions['J'].width = 10
            worksheet1.column_dimensions['K'].width = 40
            

            worksheet1.row_dimensions[1].height =20
            worksheet1.auto_filter.ref = "A1:J1"      

            workbook.save(pathfile)  
            print(user_name,'打分表格下载成功！')
            tkinter.messagebox.showinfo(title='Hi', message=user_name +' 打分表格下载成功！')

        elif user_name=='lizh48':  
            cursor.execute('''select count(*) from tester_performance_summary 
            where performance_category!='Leader' and performance_category!='TQM' and performance_category!='Auditor' and performance_category!='Equipment' ''')
            b=cursor.fetchone()  
            #单元格设置固定选择项
            data_validation=DataValidation(type='list',formula1='"1,2,3,4,5,6,7,8,9,10"')
            worksheet1.add_data_validation(data_validation)
            #提示内容
            data_validation.promptTitle = '打分范围：'
            data_validation.prompt = '1 ~ 10分, 均值<=6'
            #错误提示
            data_validation.errorTitle = '无效打分'
            data_validation.error = '打分范围应为1~10之间整数'

            worksheet1['A1'] = '姓名'  
            worksheet1['B1'] = '人员类别'  
            worksheet1['C1'] = '工作量'
            worksheet1['D1'] = '工作量得分'
            worksheet1['E1'] = '考试分值'
            worksheet1['F1'] = '测试能力'
            worksheet1['G1'] = '综合能力'
            worksheet1['H1'] = '额外职责'
            worksheet1['I1'] = 'Award/Audit'
            worksheet1['J1'] = '紫华打分'  
            worksheet1['K1'] = '紫华评语'   


        

            cursor.execute('''select name,performance_category,workload,workload_score,exam_score,capability_score,overall_capability_score,
            extra_responsibility_score,award_audit_score,zihua_score,zihua_comments from tester_performance_summary 
            where performance_category!='Leader' and performance_category!='TQM' and performance_category!='Auditor' and performance_category!='Equipment' ''')
            allrows=cursor.fetchall()  
            
            for row in range(2,b[0]+2):      
                worksheet1.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
                worksheet1.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle 
                worksheet1.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle 
                worksheet1.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle 
                # worksheet1.cell(row,4,value = allrows[row-2][3]).font = fontstyle3 
                worksheet1.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle 
                worksheet1.cell(row,6,value = allrows[row-2][5]).alignment = alignstyle             
                worksheet1.cell(row,7,value = allrows[row-2][6]).alignment = alignstyle 
                # worksheet1.cell(row,7,value = allrows[row-2][6]).font = fontstyle3 
                worksheet1.cell(row,8,value = allrows[row-2][7]).alignment = alignstyle 
                # worksheet1.cell(row,8,value = allrows[row-2][7]).font = fontstyle3   
                worksheet1.cell(row,9,value = allrows[row-2][8]).alignment = alignstyle 
                # worksheet1.cell(row,9,value = allrows[row-2][8]).font = fontstyle3           
                worksheet1.cell(row,10,value = allrows[row-2][9]).alignment = alignstyle1 
                worksheet1.cell(row,10,value = allrows[row-2][9]).font = fontstyle3
                # worksheet1.cell(row,10).fill = cellbackground 
                data_validation.add(worksheet1.cell(row,10))   

                worksheet1.cell(row,11,value = allrows[row-2][10]).alignment = alignstyle  
                worksheet1.cell(row,11,value = allrows[row-2][10]).font = fontstyle3
            #加一个备注说明：
            comments='说明：'
            comments1='1. 只更新上传“打分”，“评语” 二列，其它列数据供参考.'
            comments2='2. 上传前请计算一下打分均值，不要超过6.0分，否则会从最高分删减'
            comments3='3. 目前已经默认填写的分值可以根据实际情况更改.'
            worksheet1.cell(2,12,value = comments).font= fontstyle
            worksheet1.cell(3,12,value = comments1).alignment = alignstyle
            worksheet1.cell(4,12,value = comments2).alignment = alignstyle
            worksheet1.cell(5,12,value = comments3).alignment = alignstyle   

            for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle1
                worksheet1[cell].alignment = alignstyle
            for cell in ['J1','K1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle2
                worksheet1[cell].alignment = alignstyle

            worksheet1.column_dimensions['A'].width = 10
            worksheet1.column_dimensions['B'].width = 10
            worksheet1.column_dimensions['C'].width = 8
            worksheet1.column_dimensions['D'].width = 12
            worksheet1.column_dimensions['E'].width = 10
            worksheet1.column_dimensions['F'].width = 10
            worksheet1.column_dimensions['G'].width = 10
            worksheet1.column_dimensions['H'].width = 10
            worksheet1.column_dimensions['I'].width = 14
            worksheet1.column_dimensions['J'].width = 10
            worksheet1.column_dimensions['K'].width = 40
            

            worksheet1.row_dimensions[1].height =20
            worksheet1.auto_filter.ref = "A1:J1"      

            workbook.save(pathfile)  
            print(user_name,'打分表格下载成功！')
            tkinter.messagebox.showinfo(title='Hi', message=user_name +' 打分表格下载成功！')


        elif user_name=='zhangyu90':  
            cursor.execute('''select count(*) from tester_performance_summary 
            where performance_category!='Leader' and performance_category!='Auditor' and performance_category!='Equipment' ''')
            b=cursor.fetchone()  
            #单元格设置固定选择项
            data_validation=DataValidation(type='list',formula1='"1,2,3,4,5,6,7,8,9,10"')
            worksheet1.add_data_validation(data_validation)
            #提示内容
            data_validation.promptTitle = '打分范围：'
            data_validation.prompt = '1 ~ 10分, 均值<=6'
            #错误提示
            data_validation.errorTitle = '无效打分'
            data_validation.error = '打分范围应为1~10之间整数'

            worksheet1['A1'] = '姓名'  
            worksheet1['B1'] = '人员类别'  
            worksheet1['C1'] = '工作量'
            worksheet1['D1'] = '工作量得分'
            worksheet1['E1'] = '考试分值'
            worksheet1['F1'] = '测试能力'
            worksheet1['G1'] = '综合能力'
            worksheet1['H1'] = '额外职责'
            worksheet1['I1'] = 'Award/Audit'
            worksheet1['J1'] = '张玉打分'
            worksheet1['K1'] = '张玉评语'    
        
        

            cursor.execute('''select name,performance_category,workload,workload_score,exam_score,capability_score,overall_capability_score,
            extra_responsibility_score,award_audit_score,zhangyu_score,zhangyu_comments from tester_performance_summary 
            where performance_category!='Leader' and performance_category!='Auditor' and performance_category!='Equipment' ''')
            allrows=cursor.fetchall()  
            
            for row in range(2,b[0]+2):      
                worksheet1.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
                worksheet1.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle 
                worksheet1.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle 
                worksheet1.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle 
                # worksheet1.cell(row,4,value = allrows[row-2][3]).font = fontstyle3 
                worksheet1.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle 
                worksheet1.cell(row,6,value = allrows[row-2][5]).alignment = alignstyle             
                worksheet1.cell(row,7,value = allrows[row-2][6]).alignment = alignstyle 
                # worksheet1.cell(row,7,value = allrows[row-2][6]).font = fontstyle3 
                worksheet1.cell(row,8,value = allrows[row-2][7]).alignment = alignstyle 
                # worksheet1.cell(row,8,value = allrows[row-2][7]).font = fontstyle3   
                worksheet1.cell(row,9,value = allrows[row-2][8]).alignment = alignstyle 
                # worksheet1.cell(row,9,value = allrows[row-2][8]).font = fontstyle3           
                worksheet1.cell(row,10,value = allrows[row-2][9]).alignment = alignstyle1 
                worksheet1.cell(row,10,value = allrows[row-2][9]).font = fontstyle3
                # worksheet1.cell(row,10).fill = cellbackground 
                data_validation.add(worksheet1.cell(row,10))

                worksheet1.cell(row,11,value = allrows[row-2][10]).alignment = alignstyle 
                worksheet1.cell(row,11,value = allrows[row-2][10]).font = fontstyle3
            #加一个备注说明：
            comments='说明：'
            comments1='1. 只能更新上传“打分”，“评语” 二列，其它列数据供参考.'
            comments2='2. 上传前请计算一下打分均值，不要超过6.0分，否则会从最高分删减'
            comments3='3. 目前系统已经默认填写的分值可以根据实际情况更改'
            worksheet1.cell(2,12,value = comments).font= fontstyle
            worksheet1.cell(3,12,value = comments1).alignment = alignstyle
            worksheet1.cell(4,12,value = comments2).alignment = alignstyle
            worksheet1.cell(5,12,value = comments3).alignment = alignstyle

            for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle1
                worksheet1[cell].alignment = alignstyle
            for cell in ['J1','K1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle2
                worksheet1[cell].alignment = alignstyle

            worksheet1.column_dimensions['A'].width = 10
            worksheet1.column_dimensions['B'].width = 10
            worksheet1.column_dimensions['C'].width = 8
            worksheet1.column_dimensions['D'].width = 12
            worksheet1.column_dimensions['E'].width = 10
            worksheet1.column_dimensions['F'].width = 10
            worksheet1.column_dimensions['G'].width = 10
            worksheet1.column_dimensions['H'].width = 10
            worksheet1.column_dimensions['I'].width = 14
            worksheet1.column_dimensions['J'].width = 10
            worksheet1.column_dimensions['K'].width = 40

            

            worksheet1.row_dimensions[1].height =20
            worksheet1.auto_filter.ref = "A1:J1"      

            workbook.save(pathfile)  
            print(user_name,'打分表格下载成功！')
            tkinter.messagebox.showinfo(title='Hi', message=user_name +' 打分表格下载成功！')
            
        else:
            tk.messagebox.showwarning('提示',"授权人员专用！") 

    def score_upload():
        #获取用户名
        global user_name
        user_name = getpass.getuser()  
        #打开Excel,读取worksheet
        path = os.path.join(os.path.expanduser("~"), 'Desktop')  #获取桌面的路径
        filepath = path+'\Score_sheet.xlsx'
        workbook = vb.load_workbook(filepath)
        upload_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')    
        sheet_j = workbook.active
        user_name_sheet=sheet_j.title
        y = sheet_j.max_row
        print(user_name,user_name_sheet,y)

        #.建立mysql的连接
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor()
        cursor.execute('select sum(lijun_score) from tester_performance_summary')
        m=cursor.fetchone()
        # 用m[0]是否大于0来判定我是否已打分


        #判定用户名是否和打分表sheet名一致
        if user_name==user_name_sheet:
        # if user_name=='lijun24':
            
            #不同用户：lijun24,nizhao2,wangjin24,zhangyu90,lizh48分别处理
            if user_name_sheet=='lijun24':
                for r in range(2,y+2):          
                    name = sheet_j.cell(r,1).value
                    name_score=sheet_j.cell(r,19).value
                    name_comments=sheet_j.cell(r,20).value
                    cursor.execute('''update tester_performance_summary set lijun_score=%s,lijun_comments=%s,lijun_score_time=%s where name=%s''',(name_score,name_comments,upload_time,name))
                conn.commit()
                #重新统计一下分值，把我的打分算上
                cursor.execute('''update tester_performance_summary set people_score_summary=nizhao_score+zihua_score+zhangyu_score+wangjin_score+lijun_score,overall_performance_score=workload_score + overall_capability_score + extra_responsibility_score + award_audit_score + nizhao_score+zihua_score+zhangyu_score+wangjin_score+lijun_score ''')
                conn.commit()
                print(user_name,'上传完成！')
                tkinter.messagebox.showinfo(title='Hi', message=user_name +'上传成功！')

            elif user_name_sheet=='nizhao2' and m[0]==0:
                total=0
                for r in range(2,y+1):          
                    name = sheet_j.cell(r,1).value                    
                    name_score=sheet_j.cell(r,10).value
                    total=total+name_score
                    name_comments=sheet_j.cell(r,11).value
                    cursor.execute('''update tester_performance_summary set nizhao_score=%s,nizhao_comments=%s, nizhao_score_time=%s where name=%s''',(name_score,name_comments,upload_time,name))
                conn.commit()
                avg_score=total/(y-1)
                if avg_score<=6.0:
                    cursor.execute('''insert into tester_performance_score (name,avg_score,score_time)values(%s,%s,%s) ''',(user_name,avg_score,upload_time))
                    conn.commit()
                    print(user_name,'上传完成！打分均值：',avg_score)
                    tkinter.messagebox.showinfo(title='Hi', message=user_name +'上传成功！打分均值：'+str(avg_score))
                else:
                    tkinter.messagebox.showinfo(title='Hi', message=user_name +'请确保打分均值不超过6.0！')

            elif user_name_sheet=='wangjin24' and m[0]==0:
                total=0
                for r in range(2,y+1):          
                    name = sheet_j.cell(r,1).value    
                    extra_score=sheet_j.cell(r,9).value
                    name_score=sheet_j.cell(r,10).value
                    total=total+name_score
                    name_comments=sheet_j.cell(r,11).value
                    cursor.execute('''update tester_performance_summary set extra_responsibility_score=%s,wangjin_score=%s,wangjin_comments=%s,wangjin_score_time=%s where name=%s''',(extra_score,name_score,name_comments,upload_time,name))
                conn.commit()
                avg_score=total/(y-1)
                if avg_score<=6.0:
                    cursor.execute('''insert into tester_performance_score (name,avg_score,score_time)values(%s,%s,%s) ''',(user_name,avg_score,upload_time))
                    conn.commit()                
                    print(user_name,'上传完成！打分均值：',avg_score)
                    tkinter.messagebox.showinfo(title='Hi', message=user_name +'上传成功！打分均值：'+str(avg_score))
                else:
                    tkinter.messagebox.showinfo(title='Hi', message=user_name +'请确保打分均值不超过6.0！')

            elif user_name_sheet=='lizh48' and m[0]==0:
                total=0
                for r in range(2,y+1):          
                    name = sheet_j.cell(r,1).value    
                    name_score=sheet_j.cell(r,10).value
                    total=total+name_score
                    name_comments=sheet_j.cell(r,11).value
                    cursor.execute('''update tester_performance_summary set zihua_score=%s,zihua_comments=%s,zihua_score_time=%s where name=%s''',(name_score,name_comments,upload_time,name))
                conn.commit()
                avg_score=total/(y-1)
                if avg_score<=6.0:
                    cursor.execute('''insert into tester_performance_score (name,avg_score,score_time)values(%s,%s,%s) ''',(user_name,avg_score,upload_time))
                    conn.commit()                
                    print(user_name,'上传完成！打分均值：',avg_score)
                    tkinter.messagebox.showinfo(title='Hi', message=user_name +'上传成功！打分均值：'+str(avg_score))
                else:
                    tkinter.messagebox.showinfo(title='Hi', message=user_name +'请确保打分均值不超过6.0！')

            elif user_name_sheet=='zhangyu90' and m[0]==0:
                total=0
                for r in range(2,y+1):          
                    name = sheet_j.cell(r,1).value    
                    name_score=sheet_j.cell(r,10).value
                    
                    total=total+name_score
                    name_comments=sheet_j.cell(r,11).value
                    cursor.execute('''update tester_performance_summary set zhangyu_score=%s,zhangyu_comments=%s,zhangyu_score_time=%s where name=%s''',(name_score,name_comments,upload_time,name))
                conn.commit()
                avg_score=total/(y-1)
                if avg_score<=6.0:
                    cursor.execute('''insert into tester_performance_score (name,avg_score,score_time)values(%s,%s,%s) ''',(user_name,avg_score,upload_time))
                    conn.commit()                
                    print(user_name,'上传完成！打分均值：',avg_score)
                    tkinter.messagebox.showinfo(title='Hi', message=user_name +'上传成功！打分均值：'+str(avg_score))
                else:
                    tkinter.messagebox.showinfo(title='Hi', message=user_name +'请确保打分均值不超过6.0！')
            else:
                tkinter.messagebox.showinfo(title='Hi', message=user_name +' 打分已结束，无法上传！')


        else:
            print('用户名与表格名不符！')

    def score_report_download():
        tk.messagebox.showwarning('提示',"导出的报告请注意保密，不要和他人分享!") 

        #获取用户名
        global user_name
        user_name = getpass.getuser()  
        #待获取名单
        global namelist
        namelist=['lijun24','nizhao2','wangjin24'] #定义可以下载打分表的电脑
        #建立mysql的连接
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor()

        cursor.execute('select sum(lijun_score) from tester_performance_summary')
        m=cursor.fetchone()
        # 用m[0]是否大于0来判定我是否已打分

        path = os.path.join(os.path.expanduser("~"), 'Desktop')  #获取桌面的路径
        pathfile = path+'\绩效汇总报告.xlsx'
        workbook = vb.Workbook(pathfile)
        workbook.save(pathfile)
        workbook=vb.load_workbook(pathfile)
        workbook.create_sheet(user_name)
        workbook.remove(workbook['Sheet'])
        workbook.save(pathfile)
        worksheet1=workbook[user_name]

        fontstyle = vb.styles.Font(name='Times New Roman',bold=True,size=10)  #标题
        fontstyle1 = vb.styles.Font(name='Arial',bold=True,size=10)    #标题
        fontstyle2 = vb.styles.Font(name='Arial',bold=True,size=10,color='0000FF')  #蓝色
        fontstyle3 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文兰色
        fontstyle4 = vb.styles.Font(name='Arial',size=10,color='DC143C')  #正文红色
        alignstyle = vb.styles.Alignment(horizontal='left',vertical='center')  #居中
        alignstyle1 = vb.styles.Alignment(horizontal='center',vertical='center')  #居中
        cellbackground= vb.styles.PatternFill(fill_type='lightGray') 

        if user_name in namelist and m[0]>0:  
            cursor.execute('''select count(*) from tester_performance_summary ''')
            b=cursor.fetchone()  
            
            worksheet1['A1'] = '姓名'  
            worksheet1['B1'] = '人员类别'  
            worksheet1['C1'] = '工作量'
            worksheet1['D1'] = '工作量得分'
            worksheet1['E1'] = '考试分值'
            worksheet1['F1'] = '测试能力'
            worksheet1['G1'] = '综合能力'
            worksheet1['H1'] = '额外职责'
            worksheet1['I1'] = 'Award/Audit'
            worksheet1['J1'] = '打分合计'
 
            worksheet1['K1'] = '绩效总分'        

            cursor.execute('''select name,performance_category,workload,workload_score,exam_score,capability_score,overall_capability_score,
            extra_responsibility_score,award_audit_score,people_score_summary,overall_performance_score from tester_performance_summary order by overall_performance_score desc ''')
            allrows=cursor.fetchall()  
            
            for row in range(2,b[0]+2):      
                worksheet1.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
                worksheet1.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle 
                worksheet1.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle 
                worksheet1.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle 
                worksheet1.cell(row,4,value = allrows[row-2][3]).font = fontstyle3 
                worksheet1.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle 
                worksheet1.cell(row,6,value = allrows[row-2][5]).alignment = alignstyle             
                worksheet1.cell(row,7,value = allrows[row-2][6]).alignment = alignstyle 
                worksheet1.cell(row,7,value = allrows[row-2][6]).font = fontstyle3 
                worksheet1.cell(row,8,value = allrows[row-2][7]).alignment = alignstyle 
                worksheet1.cell(row,8,value = allrows[row-2][7]).font = fontstyle3 
                worksheet1.cell(row,9,value = allrows[row-2][8]).alignment = alignstyle 
                worksheet1.cell(row,9,value = allrows[row-2][8]).font = fontstyle3 
                worksheet1.cell(row,10,value = allrows[row-2][9]).alignment = alignstyle 
 
                worksheet1.cell(row,11,value = allrows[row-2][10]).alignment = alignstyle1 
                worksheet1.cell(row,11,value = allrows[row-2][10]).font = fontstyle3 

            for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle1
                worksheet1[cell].alignment = alignstyle
            for cell in ['D1','G1','H1','I1','U1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle2
                worksheet1[cell].alignment = alignstyle

            worksheet1.column_dimensions['A'].width = 10
            worksheet1.column_dimensions['B'].width = 10
            worksheet1.column_dimensions['C'].width = 8
            worksheet1.column_dimensions['D'].width = 12
            worksheet1.column_dimensions['E'].width = 10
            worksheet1.column_dimensions['F'].width = 10
            worksheet1.column_dimensions['G'].width = 10
            worksheet1.column_dimensions['H'].width = 10
            worksheet1.column_dimensions['I'].width = 12
            worksheet1.column_dimensions['J'].width = 10
            worksheet1.column_dimensions['K'].width = 10
            

            worksheet1.row_dimensions[1].height =20
            worksheet1.auto_filter.ref = "A1:K1"      

            workbook.save(pathfile)  
            print(user_name,'绩效汇总报告下载成功！')
            tkinter.messagebox.showinfo(title='Hi', message=user_name +': 绩效汇总报告下载成功！')
        else:
            tkinter.messagebox.showinfo(title='Hi', message=user_name +' ：在所有人打分完全结束后才可以下载！')




    def score_reset():
        

        #获取用户名
        global user_name
        user_name = getpass.getuser()  
        #待获取名单
        global namelist
        namelist=['lijun24'] 
        #建立mysql的连接
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor()
        if user_name in namelist:
            cursor.execute('update tester_performance_summary set award_audit_score=0  ')
            cursor.execute('update tester_performance_summary set extra_responsibility_score=0  ')
            cursor.execute('update tester_performance_summary set wangjin_score=5  ')
            cursor.execute('update tester_performance_summary set zhangyu_score=5  ')
            cursor.execute('update tester_performance_summary set zihua_score=5  ')
            cursor.execute('update tester_performance_summary set nizhao_score=5  ')
            cursor.execute('update tester_performance_summary set lijun_score=0  ')

            cursor.execute('''update tester_performance_summary set zihua_score=7 where performance_category='Auditor' or performance_category='Leader' or performance_category='TQM' or performance_category='Equipment' ''')
            cursor.execute('''update tester_performance_summary set wangjin_score=7 where performance_category='Auditor' or performance_category='Leader' or performance_category='TQM' or performance_category='Equipment' ''')       
            cursor.execute('''update tester_performance_summary set nizhao_score=7 where performance_category='Auditor' or performance_category='Leader' or performance_category='TQM' or performance_category='Equipment' ''')       
            cursor.execute('''update tester_performance_summary set zhangyu_score=7 where performance_category='Auditor' or performance_category='Leader' or performance_category='TQM' or performance_category='Equipment' ''')       
            #复位时额外职责分，特殊岗位定为20分，加减分定为+15分           
            cursor.execute('''update tester_performance_summary set extra_responsibility_score=20 where performance_category='Auditor' or performance_category='Leader' or performance_category='Equipment' ''')

            cursor.execute('update tester_performance_summary set people_score_summary=0')
            cursor.execute('update tester_performance_summary set overall_performance_score=0')
            cursor.execute("update tester_performance_summary set wangjin_comments='' ")
            cursor.execute("update tester_performance_summary set nizhao_comments='' ")
            cursor.execute("update tester_performance_summary set zihua_comments='' ")
            cursor.execute("update tester_performance_summary set zhangyu_comments='' ")
            cursor.execute("update tester_performance_summary set lijun_comments='' ")

            conn.commit()
            tkinter.messagebox.showinfo(title='Hi', message='打分表复位成功！')
        else:            
            tk.messagebox.showwarning('提示',"复位功能仅限管理员使用!") 



    #下载加减分模板
    def score_template_download():
        tk.messagebox.showwarning('提示',"加减分模板中请不要添加无效内容!") 

        #获取用户名
        global user_name
        user_name = getpass.getuser()  
        #待获取名单
        global namelist
        namelist=['lijun24','nizhao2','wangjin24','zhangyu90','lizh48'] #定义可以下载打分表的电脑
        #建立mysql的连接
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor()

        path = os.path.join(os.path.expanduser("~"), 'Desktop')  #获取桌面的路径
        pathfile = path+'\加减分模板.xlsx'
        workbook = vb.Workbook(pathfile)
        workbook.save(pathfile)
        workbook=vb.load_workbook(pathfile)
        workbook.create_sheet(user_name)
        workbook.remove(workbook['Sheet'])
        workbook.save(pathfile)
        worksheet1=workbook[user_name]

        fontstyle = vb.styles.Font(name='Times New Roman',bold=True,size=10)  #标题
        fontstyle1 = vb.styles.Font(name='Arial',bold=True,size=10)    #标题
        fontstyle2 = vb.styles.Font(name='Arial',bold=True,size=10,color='0000FF')  #蓝色
        fontstyle3 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文兰色
        fontstyle4 = vb.styles.Font(name='Arial',size=10,color='DC143C')  #正文红色
        alignstyle = vb.styles.Alignment(horizontal='left',vertical='center')  #居中
        alignstyle1 = vb.styles.Alignment(horizontal='center',vertical='center')  #居中
        cellbackground= vb.styles.PatternFill(fill_type='lightGray') 

        if user_name in namelist:  
            cursor.execute('''select * from auditrecord where no=1170 ''')
            allrows=cursor.fetchone()  
            
            worksheet1['A1'] = 'Category'  
            worksheet1['B1'] = 'Technician'  
            worksheet1['C1'] = 'Engineer'
            worksheet1['D1'] = 'Product'
            worksheet1['E1'] = 'Test_no'
            worksheet1['F1'] = 'Test_name'
            worksheet1['G1'] = 'Description'
            worksheet1['H1'] = 'Score'
    
            
           
            worksheet1.cell(2,1,value = allrows[1]).alignment = alignstyle 
            worksheet1.cell(2,2,value = allrows[3]).alignment = alignstyle 
            worksheet1.cell(2,3,value = allrows[4]).alignment = alignstyle 
            worksheet1.cell(2,4,value = allrows[5]).alignment = alignstyle     
            worksheet1.cell(2,5,value = allrows[6]).alignment = alignstyle 
            worksheet1.cell(2,6,value = allrows[7]).alignment = alignstyle             
            worksheet1.cell(2,7,value = allrows[8]).alignment = alignstyle    
            worksheet1.cell(2,8,value = allrows[10]).alignment = alignstyle 
   
     
  

            for cell in ['A1','B1','C1','D1','E1','F1','G1','H1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle1
                worksheet1[cell].alignment = alignstyle
            for cell in ['H1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle2
                worksheet1[cell].alignment = alignstyle

            worksheet1.column_dimensions['A'].width = 12
            worksheet1.column_dimensions['B'].width = 10
            worksheet1.column_dimensions['C'].width = 10
            worksheet1.column_dimensions['D'].width = 12
            worksheet1.column_dimensions['E'].width = 12
            worksheet1.column_dimensions['F'].width = 12
            worksheet1.column_dimensions['G'].width = 50
            worksheet1.column_dimensions['H'].width = 10


            workbook.save(pathfile)  
            print(user_name,'加减分模板下载成功！')
            tkinter.messagebox.showinfo(title='Hi', message=user_name +': 加减分模板下载成功！')
        else:
            tkinter.messagebox.showinfo(title='Hi', message=user_name +' ：授权人员才可以下载！')

    #加减分批量模板上传
    def score_batch_upload():
        #获取用户名
        global user_name
        user_name = getpass.getuser()  
        #打开Excel,读取worksheet
        path = os.path.join(os.path.expanduser("~"), 'Desktop')  #获取桌面的路径
        filepath = path+'\加减分模板.xlsx'
        workbook = vb.load_workbook(filepath)
   
        sheet_j = workbook.active
        user_name_sheet=sheet_j.title
        y = sheet_j.max_row
        print(user_name,user_name_sheet,y)

        #.建立mysql的连接
        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
        cursor = conn.cursor()    

        #判定用户名是否和打分表sheet名一致
        if user_name==user_name_sheet:

            for r in range(3,y+1):          
                category = sheet_j.cell(r,1).value                  
                technician=sheet_j.cell(r,2).value
                engineer=sheet_j.cell(r,3).value
                product = sheet_j.cell(r,4).value
                test_no=sheet_j.cell(r,5).value
                test_name=sheet_j.cell(r,6).value     
                description=sheet_j.cell(r,7).value
                score=sheet_j.cell(r,8).value   
                update_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')   
                if engineer is None:
                    engineer='N/A'
                if product is None:
                    product='N/A'
                if test_no is None:
                    test_no='N/A'
                if test_name is None:
                    test_name='N/A'
                #7月9日修改：解决有时会上传空白数据的问题。           
                if score==1:                    
                    cursor.execute('''insert into auditrecord (category,date,technician,engineer,product,test_no,test_name,description,auditor,score,update_time)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''',(category,todaycolumn,technician,engineer,product,test_no,test_name,description,user_name,score,update_time))
                    # auto_email(category,technician,description,user_name)
                if score==-1:                    
                    cursor.execute('''insert into auditrecord (category,date,technician,engineer,product,test_no,test_name,description,auditor,score,update_time)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''',(category,todaycolumn,technician,engineer,product,test_no,test_name,description,user_name,score,update_time))
                 
            conn.commit()
            print(user_name,'上传完成！')
            tkinter.messagebox.showinfo(title='Hi', message=user_name +'打分上传成功，可以通过Display查看与删除最后15条数据！注意不要重复上传！')

        else:
            print('用户名与表格名不符！')
 
    def sw_exit(): 
        sys.exit()   
    #定义Execute菜单
    menubar=tk.Menu(window,)
    execute_menu=tk.Menu(menubar,tearoff=0,background='lightblue', foreground='black')  #tearoff指是否分开，1能分开
    menubar.add_cascade(label='Management Tool',menu=execute_menu)
    execute_menu.add_command(label='打分表下载',command=score_sheet_download)
    execute_menu.add_command(label='打分上传',command=score_upload)
    execute_menu.add_command(label='绩效报告生成',command=score_report_download)    
    execute_menu.add_separator() #加一条线
    execute_menu.add_command(label='加减分模板下载',command=score_template_download)
    execute_menu.add_command(label='加减分批量上传',command=score_batch_upload)
    execute_menu.add_separator() #加一条线
    if user_name == 'lijun24':
        execute_menu.add_command(label='绩效数据收集', command=data_collection)
        execute_menu.add_command(label='打分复位',command=score_reset)
    execute_menu.add_command(label='退出',command=sw_exit)



    def Balloon_show(event,msg,fg=None,bg=None):#前景色和背景色可有可无，往后可以增加字体自定义
        try:
            balloon.grid_forget()
        except:
            pass#如果有存在的气泡提示框，那么隐藏，但不销毁
        balloon['fg']=fg    
        balloon['bg']=bg    
        balloon['text']=msg    
        balloon.grid(row=18,column=4,sticky='w',columnspan=4)     


    def Balloon_destroy(event):
        try:
            balloon.grid_forget()  #不能用place_forget(),只能用同一种
        except:
            pass
    
    #使用bind时必须把相应控件布局放在bind之后
    button0.bind('<Enter>',lambda event:Balloon_show(event,msg='-> New record input one by one',fg='red',bg='Yellow')) 
    button0.bind('<Leave>',Balloon_destroy)
    button0.grid(row=16,column=2) 
    button1.bind('<Enter>',lambda event:Balloon_show(event,msg='''->Disply last 15 records in past 30days''',fg='red',bg='Yellow')) 
    button1.bind('<Leave>',Balloon_destroy)
    button1.grid(row=16,column=4)   
    button2.bind('<Enter>',lambda event:Balloon_show(event,msg='''->Submit new input/update old input''',fg='red',bg='Yellow')) 
    button2.bind('<Leave>',Balloon_destroy)
    button2.grid(row=16,column=6)         
    button4.bind('<Enter>',lambda event:Balloon_show(event,msg='->Export records to Excel file/Send out email summary',fg='red',bg='Yellow')) 
    button6.bind('<Enter>',lambda event:Balloon_show(event,msg='->Export audit list to Excel file',fg='red',bg='Yellow')) 
    #指定可以进行报告输出及邮件发送的人员名单
    if user_name == 'wangjin24' or user_name == 'lijun24' or user_name == 'nizhao2' or user_name == 'zhangyu90' or user_name == 'huangyl7':   
    
        button4.bind('<Leave>',Balloon_destroy)
        button4.grid(row=17,column=4)  

        button6.bind('<Leave>',Balloon_destroy)
        button6.grid(row=17,column=6)  

    new()  #开始运行时给一行空格

    
    if user_name != 'wangjin24' and user_name != 'zhangyu90' and user_name != 'huangyl7' and user_name != 'Lijun24' and user_name != 'lijun24' and user_name != 'nizhao2'  and user_name != 'zhangyq14' and user_name != 'liaoqh1' and user_name != 'chenkai12' and user_name != 'zhangcx1' and user_name != 'wanghuan32' and user_name != 'lizh48' and user_name != 'pengch2' and user_name != 'zhaojk2' and user_name != 'zouff1' and user_name != 'xiongcq2' and user_name != 'liujin4':        
        tkinter.messagebox.showwarning(title='Hi', message='如需使用软件，请首先联系倪昭王锦获取使用授权！谢谢！')
        sys.exit()

 
    todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')     
    conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')    
    cursor = conn.cursor() 

    cursor.execute('''select max(version) from version_info where sw_name='GTS' ''')
    ver_infor=cursor.fetchone()
    ver_number=ver_infor[0]
    print(ver_number,version)
    if ver_number < version:
        cursor.execute('''INSERT INTO version_info (sw_name,VERSION,DATE,comments)VALUES('GTS',%s,%s,%s) ''',(version,todaycolumn,comments))
        conn.commit()

    if ver_number > version:
        address=r'\\10.114.184.9\tcc_rel\03 Software\12 自动化小工具'
        tkinter.messagebox.showwarning(title='Hi', message='软件已升级到 V'+str(ver_number)+'！ 请到服务器下载替换！' + address)
        sys.exit()

    window.config(menu=menubar)  #定义菜单时必须加上这句
    window.mainloop()






if __name__=="__main__": 
    
    # ip_address='localhost'
    ip_address='10.114.183.17'
    # ip_address='10.114.183.55'
    # ip_address='10.114.183.127'
    #ip_address=str(input('请输入数据库ip地址：'))
    version=3.7
    current_ver_name='Award_audit_record_V3.7'
    last_ver_name='Award_audit_record_V3.6'

    release_date='2025-03-15'
    comments='新财年绩效汇总更新！'
    main()

 
